Finding group maxes in SQL join result

Question

Possible Duplicate:
SQL: Select first row in each GROUP BY group?

Two SQL tables. One contestant has many entries:

Contestants     Entries
Id   Name       Id  Contestant_Id  Score
--   ----       --  -------------  -----
1    Fred       1   3              100
2    Mary       2   3              22
3    Irving     3   1              888
4    Grizelda   4   4              123
                5   1              19
                6   3              50

Low score wins. Need to retrieve current best scores of all contestants ordered by score:

Best Entries Report
Name     Entry_Id  Score
----     --------  -----
Fred     5         19
Irving   2         22
Grizelda 4         123

I can certainly get this done with many queries. My question is whether there's a way to get the result with one, efficient SQL query. I can almost see how to do it with GROUP BY, but not quite.

In case it's relevant, the environment is Rails ActiveRecord and PostgreSQL.

score 1 · Answer 1 · answered Dec 02 '12 at 21:09

1

The easiest way to do this is with the ranking functions:

select name, Entry_id, score
from (select e.*, c.name,
             row_number() over (partition by e.contestant_id order by score) as seqnum
      from entries e join
           contestants c
           on c.Contestant_id = c.id
     ) ec
where seqnum = 1

answered Dec 02 '12 at 21:09

Gordon Linoff

1,242,037
58
646
786

Not the easiest way. For this particular task (pick one record for every distinct value) postgresql has special clause - `DISTINCT ON ()`. See my answer for details. – Ihor Romanchenko Dec 02 '12 at 23:11

score 1 · Answer 2 · answered Dec 02 '12 at 21:11

1

I'm not familiar with PostgreSQL, but something along these lines should work:

SELECT c.*, s.Score
FROM Contestants c
JOIN (SELECT MIN(Score) Score, Contestant_Id FROM Entries GROUP BY Contestant_Id) s
ON c.Id=s.Contestant_Id

answered Dec 02 '12 at 21:11

Jon Hulka

1,259
10
15

score 1 · Answer 3 · answered Dec 02 '12 at 21:17

1

one of solutions is

select min(e.score),c.name,c.id from entries e
inner join contestants c on e.contestant_id = c.id
group by e.contestant_id,c.name,c.id

here is example http://sqlfiddle.com/#!3/9e307/27

answered Dec 02 '12 at 21:17

xurca

2,426
3
24
29

score 1 · Answer 4 · answered Dec 02 '12 at 21:18

1

This simple query should do the trick..

Select contestants.name as name, entries.id as entry_id,  MIN(entries.score) as score
FROM entries
JOIN contestants ON contestants.id = entries.contestant_id
GROUP BY name
ORDER BY score

this grabs the min score for each contestant and orders them ASC

answered Dec 02 '12 at 21:18

Alex Naspo

2,052
1
20
37

This is clean, but unfortunatley PosgreSQL is requiring the GROUP BY to include the entry_id field, which makes grouping useless. – Gene Dec 03 '12 at 04:40

Ihor Romanchenko · Accepted Answer · 2012-12-02T23:18:57.603

Here is specific postgresql way of doing this:

SELECT DISTINCT ON (c.id) c.name, e.id, e.score
FROM Contestants c
JOIN Entries e ON c.id = e.Contestant_id
ORDER BY c.id, e.score

Details about DISTINCT ON are here.

My SQLFiddle with example.

UPD To order the results by score:

SELECT *
FROM (SELECT DISTINCT ON (c.id) c.name, e.id, e.score
      FROM Contestants c
      JOIN Entries e ON c.id = e.Contestant_id
      ORDER BY c.id, e.score) t
ORDER BY score

Finding group maxes in SQL join result

5 Answers5