CODEWITHSUNDEEP
sqlsql-server
Master_T
Master_T

Reputation: 7943

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?

Upvotes: 1

Views: 575

Answers (1)

Radioleao
Radioleao

Reputation: 404

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.

Upvotes: 3

Related Questions