How to Display the name of singer who has the maximum number of songs

Question

Hi I am beginner and i want to Show that singer name who has maximum number of songs in songs table but i failed to do this because subquery cannot return two values at a time. how can i solve this problem. Below code shows this error -> Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

here is my code ->

SELECT Singer_Name
FROM Singer
WHERE Singer_id IN(SELECT TOP 1 Singer.Singer_id,COUNT(SongTitle) TotalSounds 
                    FROM Singer,Songs
                    WHERE Songs.Singer_id=Singer.Singer_id
                    GROUP BY Singer.Singer_id
                    ORDER BY TotalSounds DESC)

Answer 1

You were close to a workable solution. The issue is you were doing the subquery in the where clause, trying to limit your results that way, where you could just return the full list of names and number of songs and just pick the top one after ordering by the count:

SELECT TOP 1 SingerName FROM (SELECT Singer.Singer_Name, count(1) as TotalSounds 
FROM Singer 
JOIN Songs
ON Songs.Singer_id=Singer.Singer_id
GROUP BY Singer.Singer_Name, Singer.Singer_id) ss
ORDER BY TotalSounds DESC

If you have a tie for first place, and want to return both names, you make it TOP 1 WITH TIES otherwise it will just grab the first one, arbitrarily breaking the tie by the order they appear in the table (likely by Singer_id)