Find the id that has completed the maximum number of lessons-CodePudding

Have a request (max_cl = maximum number of lessons)

> select s.id, count(s.id_class) as max_cl
> from classes as s 
>     join teachers as t on s.id=t.id
>  group by s.id
>  order by max_cl desc

This request gives this [1]: https://i.stack.imgur.com/U3v7P.png

How can i display only one id corresponding max_cl use "having", but not like this?

> select s.id, count(s.id_class) as max_cl
> from classes as s 
>     join teachers as t on s.id=t.id
>  group by s.id
>  having count(s.id_class) = 948 
>  order by max_cl desc

CodePudding user response：

Would

select * from (
 select s.id, count(s.id_class) as max_cl
 from classes as s 
     join teachers as t on s.id=t.id
  group by s.id
  order by max_cl desc
) where rownum = 1

work?

[EDIT] You could also try tmp table expression and a window function counting the lines:

with tmp as (
 select s.id, count(s.id_class) as max_cl
       , row_number() over(order by count(s.id_class) desc) as rn
 from classes as s 
     join teachers as t on s.id=t.id
  group by s.id
  order by max_cl desc
)
select id, max_cl
from tmp 
where rn = 1