Postgresql vlookup

Question

Let's say I have a table "uservalue" with the following columns:

integer user_id
integer group_id
integer value

I can get the maximum value for each group easily:

select max(value) from uservalue group by group_id;

What I would like is for it to return the user_id in each group that had the highest value. The max function in matlab will also return the index of the maximum, is there some way to make postgresql do the same thing?

Answer 1

The proper way todo this is with a subquery.

select 
    u.user_id,
    u.value 
from 
     uservalue u
join
    (select groupid, max(value) as max_value from uservalue group by group_id) mv
    on u.value = mv.max_value and mv.group_id = u.group_id

However I sometimes prefer a simpler hack.

select max(value*100000 + user_id) - 100000, max(value) from user_value group by group_id

Making sure that number (100000) is higher than any userids you are expecting to have. This makes sure only one user_id is selected on the same values whilst the other one selects them both.

Answer 2

Seems you should be able to do this with a windowing query, something like:

SELECT DISTINCT 
  group_id,
  first_value(user_id) OVER w AS user,
  first_value(value) OVER w AS val
FROM
  uservalue
 WINDOW w AS (PARTITION BY group_id ORDER BY value DESC)

This query will also work if you have multiple users with the same value (unless you add a second column to ORDER BY you will not know which one you will get back though - but you will only get one row back per group)

Answer 3

Here are several ways to do this.

It's pretty much a FAQ.