CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Alk
Alk

Reputation: 5567

Maximum values from SELECT SQL Query

I have a query which returns results in the following format: 

User1 TypeA 3
User1 TypeB 29
User1 TypeC 100
User2 TypeA 221
User2 TypeB 31
User2 TypeC 32
User3 TypeA 21
User3  TypeB 1
User3 TypeC 10
....

So basically for each user in the database, it lists 3 rows, each corresponding to TypeA, TypeB, TypeC. Now I need to return the user ID which corresponds to the maximum value for each type. So for example, from the data above we would obtain: 

TypeA User2
TypeB User2
TypeC User1

I'm not sure how to proceed with this in SQL.

Upvotes: 0

Views: 93

Answers (2)

Parshuram Kalvikatte
Parshuram Kalvikatte

Reputation: 1646

Try this 

 SELECT X.col2,X.col1 FROM
    (SELECT col1,col2,col3,row_number() over (partition by col2 order by col3 desc) as seq,
    FROM table1)X
    WHERE X.seq = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271131

A typical way of doing this uses row_number():

with t as (
      <your query here>
     )
select t.*
from (select t.*,
             row_number() over (partition by typecol order by col3 desc) as seqnum
      from t
     ) t
where seqnum = 1;

Postgres offers another solution which is typically more efficient:

select distinct on (typecol) q.*
from (<your query here>) q
order by typecol, col3 desc;

Upvotes: 1

Related Questions