Tuğçe Demir Tuğçe Demir - 6 months ago 12
SQL Question

Display the top two most popular activities across all reservations

The Table is

SUPERVISION (ResNo, ActivityID, SupervisorID, Day, Time)


I have done something like this but it is wrong

SELECT COUNT(S.Res, S.ActivityID) AS PopularActivities
FROM Supervision S
WHERE rownum = 2;
ORDER BY COUNT(*) DESC;


or

SELECT S.ResNo, S.ActivityID
FROM Supervision S
WHERE (rank() over (order by count(*) DESC) as RNK
from Supervision S) AND rnk = 2;

Answer

You need to put it in a subquery:

SELECT *
FROM (
    SELECT
        ActivityID, COUNT(*) AS Cnt
    FROM Supervision
    GROUP BY ActivityID
    ORDER BY Cnt DESC
) t
WHERE rownum <= 2

Alternatively, you can use RANK to achieve the same result:

SELECT *
FROM (
    SELECT
        ActivityID, 
        RANK() OVER(PARTITION BY ActivityID ORDER BY COUNT(*) DESC) AS rnk
    FROM Supervision
    GROUP BY ActivityID
) t
WHERE rnk <= 2