Query only records with max value within a group

user_658651 0
Viewed 1383

Say you have the following users table on PostgreSQL:

id | group_id |    name | age
---|----------|---------|----
 1 |        1 |    adam |  10
 2 |        1 |     ben |  11
 3 |        1 | charlie |  12   <-
 3 |        2 |  donnie |  20  
 4 |        2 |    ewan |  21   <-
 5 |        3 |    fred |  30   <-

How can I query all columns only from the oldest user per group_id (those marked with an arrow)?

I've tried with group by, but keep hitting "users.id" must appear in the GROUP BY clause.

(Note: I have to work the query into a Rails AR model scope.)

postgresql activerecord ruby-on-rails
3 Answers

After some digging, you can do use PostgreSQL's DISTINCT ON (col):

select distinct on (users.group_id) users.*
from users
order by users.group_id, users.age desc;
-- you might want to add extra column in ordering in case 2 users have the same age for same group_id

Translated in Rails, it would be:

User
  .select('DISTINCT ON (users.group_id), users.*')
  .order('users.group_id, users.age DESC')

Some doc about DISTINCT ON: https://www.postgresql.org/docs/9.3/sql-select.html#SQL-DISTINCT

Working example: https://www.db-fiddle.com/f/t4jeW4Sy91oxEfjMKYJpB1/0

user_9767750

You could use ROW_NUMBER/RANK(if ties are possible) windowed functions:

SELECT * 
FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY age DESC) AS rn
      FROM tab) s
WHERE s.rn = 1;
user_50708790

you can use a subquery wuth aggreagated resul in join 

select m.* 
from  users m
inner join (
  select  group_id, max(age) max_age
  from users 
  group by group_id
) AS t on (t.group_id = m.group_id and t.max_age  = m.age)
user_35223120
Related