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.

Answer 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;