Thursday, November 30, 2017

SQL SERVER - select with having clause

When you need only rows, where their count in group is for example greater than 1, use this code with having clause:
select prodid, itemid, index, count(*)
from cieb_ivecofvvlabels
group by prodid, itemid, index
having count(*) > 1;
Output:
prodid               itemid                                   index       count
-------------------- ---------------------------------------- ----------- -----------
17-045164            5801-285-899_0_0000                      1           2
17-045164            5801-285-899_0_0000                      2           2
17-045164            5801-285-899_0_0000                      3           2
17-045164            5801-285-899_0_0000                      4           2
17-045165            5801-285-899_0_0000                      1           2
...
Another variant is to use subselect on base select.

No comments:

Post a Comment