CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
svoop
svoop

Reputation: 3454

Query only records with max value within a group

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.)

Upvotes: 2

Views: 1979

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133370

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)

Upvotes: 1

MrYoshiji
MrYoshiji

Reputation: 54882

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

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

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;

Upvotes: 1

Related Questions