Re: Good Way to SELECT Max Occurence

"dd" <dd_at_dd.com> wrote in message news:42a6c32d$1_3_at_rain.i-cable.com...
> 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(*).

select student_ID, CNT from (

  select student_ID, count(*) CNT

     , RANK() OVER ( ORDER BY count(*) DESC) N from student_enrollment

  group by student_ID

) where N=1 Received on Wed Jun 08 2005 - 06:54:35 CDT