Maximum values from SELECT SQL Query

Question

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.

Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Nov 8, 2016 at 12:34

Gordon Linoff · Accepted Answer · 2016-11-08 12:35:47Z

1

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;

answered Nov 8, 2016 at 12:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Parshuram Kalvikatte · Accepted Answer · 2016-11-08 13:03:13Z

0

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

answered Nov 8, 2016 at 13:03

Parshuram Kalvikatte

1,6465 gold badges23 silver badges45 bronze badges

Collectives™ on Stack Overflow

Maximum values from SELECT SQL Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related