Master_T Master_T - 7 months ago 10
SQL Question

SQL: default behavior when using GROUP BY

Let's say I have the following query:

SELECT colA, colB, colC, colD
FROM table
GROUP BY colC, colD


This query doesn't run on
MS SQL Server
, because columns
colA
and
colB
are not part of the group by and are not in an aggregate.

Now, I know that all the other columns will have the same values inside each group. So I could fix this by choosing an aggregate like
MAX
for
colA
and
colB
, or just add them to the
GROUP BY
.

However, here's my problem: the above query is just an example, but what I really have are several very long queries with lots of columns. What I want to know is: can I set a default aggregate function for the remaining columns or something similar, so I don't have to include all the remaining columns manually in the
GROUP BY
or apply an aggregate manually to all the select pieces?

Answer

NO.

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.