Nikheel Rajman Nikheel Rajman - 3 months ago 14
SQL Question

sql joins and finding most common value

I have 2 tables, EPISODES and PRESENTERS (please see attached image 1). I want to find the the presenter who has done the most number of shows and display it in a format like this:

TIFF NEEDELL 5

I have already found the most common presenter using this query.

select PRESENTERID,count(*) as NumberOfEpisodes from dbo.EPISODES
group by PRESENTERID
having count(*) =(select max(NumberOfEpisodes) from
(select PRESENTERID,count(*) as NumberOfEpisodes from dbo.EPISODES group by PRESENTERID) EPISODES)

Answer

Using Top and ordering the count in descending order will do the trick

Select Top 1
    PRESENTERID,count(*) as NumberOfEpisodes 
From
   dbo.EPISODES    
Group By PRESENTERID
Order By NumberOfEpisodes DESC