Is this what you are looking for ?
select * from
(select rownum rn,X.*
from(select count(*) ct,student_id
from student_enrollment
group by student_id
order by ct desc) X
)Y
where Y.rn=1;
dd wrote:
> Given a table representing student's enrollment in classes:
>
> create table student_enrollment (
> student_id number,
> enrollment_id number,
> ...,
> primary key(student_id,enrollment_id)
> );
>
> The usual way to query the student_id with largest number of enrollment is:
>
> select student_id,count(*) from student_enrollment
> group by student_id
> having count(*)=(select max(count(*)) from student_enrollment group by
> student_id);
>
> But, here we need to include count(*) 3 times. I am just wondering if there
> is a more elegant approach to this kind of queries without so many count(*).
Received on Wed Jun 08 2005 - 06:35:47 CDT
