Lucky Makhado Lucky Makhado - 3 months ago 27
SQL Question

i want to display highest no. of episodes

I have the following code

SELECT PRESENTERS.PNRFNAME, PRESENTERS.PNRLNAME,
COUNT(EPISODES.PRESENTERID) AS "no. of episodes"
FROM EPISODES
JOIN PRESENTERS
ON EPISODES.PRESENTERID=PRESENTERS.PRESENTERID
GROUP BY PRESENTERS.PNRFNAME, PRESENTERS.PNRLNAME
ORDER BY COUNT(EPISODES.PRESENTERID)


these are the results

I tried adding

HAVING COUNT(EPISODES.PRESENTERID) > 2


These are the results

But I have to pretend like I haven't seen how many no. of episode are there.is there other way I can display row that have more no. of episode?

Answer
    select top 1 PRESENTERS.PNRFNAME, PRESENTERS.PNRLNAME,COUNT(EPISODES.PRESENTERID) AS "no. of episodes" FROM EPISODES JOIN PRESENTERS ON EPISODES.PRESENTERID=PRESENTERS.PRESENTERID GROUP BY PRESENTERS.PNRFNAME, PRESENTERS.PNRLNAME ORDER BY COUNT(EPISODES.PRESENTERID) desc;