Nikheel Rajman Nikheel Rajman - 1 year ago 110
SQL Question

SQL joins in MS SQL Server 2014

I have 2 tables, EPISODES and PRESENTERS

EPISODES
EPI_NO PRES_ID
1 001
2 002
3 001

PRESENTERS
PRES_ID FNAME SNAME
001 Tom Smith
002 Steve Harris


I want to write a query that finds the Presenter who has done the most number of episodes and display his FNAME and SNAME is this format:

Tom Smith 2

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


I have managed to make this query that finds the Presenter that has done the most number of episodes.

Thanks!

Answer Source

Now JOIN it with other table to get rest data like

select p.FNAME + ' ' + p.SNAME as Name,
xxx.NumberOfEpisodes
FROM PRESENTERS p
JOIN ( Select Top 1 PRES_ID,
       count(*) as NumberOfEpisodes 
       From EPISODES    
       Group By PRES_ID ) xxx ON p.PRES_ID = xxx.PRES_ID;