CODEWITHSUNDEEP
sqlsql-servergroup-by
rafacardosoc
rafacardosoc

Reputation: 251

Using MAX when selecting a high number of fields in a query

I understand some varieties of this question have been asked, but I could not find an answer to my specific scenario.

My query has over 50 fields being selected, and only one of them is an aggregate, using MAX(). On the GROUP BY clause, I would only like to pass two specific fields, name and UserID, not all 50 to make the query run. See small subset below.

SELECT 
t1.name,
MAX(t2.id) as UserID,
t3.age,
t3.height,
t3.dob,
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
LEFT JOIN table3 t3 ON t1.id = t3.id 
GROUP BY t1.name, UserID

Is there any workaround or better approach to accomplish my goal? The database is SQL Server and any help would be greatly appreciated.

Upvotes: 0

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270001

Hmmm . . . What values do you want for the other fields? If you want the max() of one column for each id and code, you can do:

select t.*
from (select t.*, max(col) over (partition by id, code) as maxcol
      from t
     ) t
where col = maxcol;

Given that id might be unique, you might want the maximum id as well as the other columns for each code:

select t.*
from (select t.*, max(id) over (partition by code) as maxid
      from t
     ) t
where id = maxid;

Upvotes: 4

Related Questions