Let's say I have the following query:
SELECT colA, colB, colC, colD
GROUP BY colC, colD
MS SQL Server
Instead, you can get only one row for every couple colC-colD, with few control of what values you get from other columns. You can use a row numbering function.
Add a column that numbers rows by group (for every couple colC-colD):
select colA, colB, colC, colD, row_number() over (partition by colC, colD order by colA, colB) as Sequence FROM table
In MSSQL you can not use a row_number in where clause. If you want to see only a row for every couple colC-colD, you must put all in a subquery:
select colA, colB, colC, colD from ( select colA, colB, colC, colD, row_number() over (partition by colC, colD order by colA, colB) as Sequence FROM table ) T where Sequence = 1
With this method you see only one row of all the other columns but you cannot choose an aggregate function.
All you can choose is the order criteria, which means which row will have the number "1", changing the part
order by colA, colB.