SQL: Grouping by column while displaying other column entries

Question

So let's say I've got two tables:

name_tbl

| INIT | NAME    | 
| JA   | Jack    | 
| JO   | John    | 
| MI   | Michael | 
| GE   | George  | 
| WI   | William | 
| BL   | Blake   | 
| PE   | Peter   |

ranking_tbl

| YEAR | INIT | RANKING
| 1990 | JA   | 5
| 1990 | JO   | 22
| 1990 | PE   | 54
| 1992 | MI   | 2
| 1992 | JA   | 14
| 1993 | GE   | 1
| 1994 | JO   | 8

I want to group by year, and display the highest ranking of that year, and the name of the person with the highest rank that year.

I'm able to display the year and ranking easily by using an aggregate function on the ranking and grouping by the year. However I'm having issues figuring out how I can display the name of the highest rank.

So far I wrote the following SQL:

SELECT r.year, min(r.ranking)
FROM RANKING_TBL r
GROUP BY r.year

I tried to Inner join the other table and select the name, but it returns me with an error saying I have to contain it in an aggregate function, or in the GROUP BY clause...

SELECT r.year, min(r.ranking), n.name
FROM ranking_tbl r
INNER JOIN name_tbl n
ON r.init = n.init
GROUP BY r.year

score 0 · Answer 1 · answered Sep 26 '16 at 20:55

You can use a subquery with an in clause on the years and the max(ranking)

 select a.year, a.ranking, b.name
 from ranking_tbl as a 
 inner join name_tbl as b  on a.init = b.init
 where (a.year, a.ranking)  in (
       select year, max(ranking)
       from ranking_tbl
       group by year )

SQL: Grouping by column while displaying other column entries

1 Answers1