user3432754 user3432754 - 1 month ago 5
SQL Question

Sql two table query most duplicated foreign key

I got those two tables

sport
and
student
:

First table
sport
:

|idsport | name |
_______________________
| 1 | bobsled |
| 2 | skating |
| 3 | boarding |
| 4 | iceskating |
| 5 | skiing |


Second table
student
:

foreign key
|idstudent | name | sport_idsport
__________________________________________
| 1 | john | 3 |
| 2 | pauly | 2 |
| 3 | max | 1 |
| 4 | jane | 2 |
| 5 | nico | 5 |


so far i did this it output which number is mostly inserted, but cant get it to work
with two tables

SELECT sport_idsport
FROM (SELECT sport_idsport FROM student GROUP BY sport_idsport ORDER BY COUNT(*) desc)
WHERE ROWNUM<=1;


I need to output name of most popular sport, in that case it would be skating.

I use oracle sql.

Answer
with counter as (
    Select sport_idsport, 
           count(*) as cnt,
           dense_rank() over (order by count(*) desc) as rn
    from student 
    group by sport_idsport
)
select s.*, c.cnt
from sport s
 join counter c on c.sport_idsport = s.idsport and c.rn = 1;

SQLFiddle example: http://sqlfiddle.com/#!4/b76e21/1

Comments