What are the differences between answers-CodePudding

i am doing sql exercises.

Question: what classrooms are the most popular

Table:

id int
date date
class int
number_pair int
teacher int
subject int
classroom int

My answer:

select classroom, count (classroom) as count
from Schedule
group by classroom
ORDER by count DESC
LIMIT 2

Correct answer:

select classroom 
from Schedule
group by classroom
having count(classroom) = (select  count (classroom) as count
from Schedule
group by classroom
ORDER by count DESC
LIMIT 1)

The result is the same but website tells my answer is wrong.

What am i missing? What is wrong with my querry?

any feedback would be highly appreciate =)

CodePudding user response：

The correct answer will return all classrooms tied for most popular; your answer will return at most two classrooms, one of the most popular, and either another tied for most popular or a second most popular.

Nowadays, a better correct answer is

select classroom
from (
    select classroom, rank() over (order by count(1)) as r
    from Schedule
    group by classroom
) ranked_classrooms
where r=1