finding the largest number of something SQL-CodePudding

So the task is this: The database contains just 5 songs released in 1953. What is the largest number of songs released in a single year?

And these are the columns that im working with from the table songs

songid - title - releasedate

My solution so far is this:

select extract(year from S.releasedate), count(S.releasedate) as most_freq
from songs S
group by extract(year from S.releasedate)
order by count(extract(year from S.releasedate)) DESC;

and the result is right but i was asked to only return the number 833 (that is the largest number of songs released in one year) and use another way than order by... any suggestions

this is my result:

CodePudding user response：

If you just want the highest count of releases this should work:

select extract(count(S.releasedate) as most_freq
from songs S
group by extract(year from S.releasedate)
order by most_freq DESC
LIMIT 1;

CodePudding user response：

This is a way I thought of without using the Order by clause that should work.

with My_CTE (year, count)
as(
select extract(year from S.releasedate), count(S.releasedate)
from songs S
group by extract(year from S.releasedate) 
) 
select max(my_cte.count)
from My_CTE

CodePudding user response：

select Convert(Varchar(4),releasedate, 120), count(releasedate) as most_freq
from songs
group by releasedate
order by count(releasedate) DESC;