Re: Good Way to SELECT Max Occurence

From: Robert Klemme <bob.news_at_gmx.net>
Date: Wed, 8 Jun 2005 13:37:59 +0200
Message-ID: <3go3lsFdf4a3U1@individual.net>

Akp wrote:

> 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;
> ===================================

I think you can even omit one level of nesting.

Untested:

select X.*
 from(select count(*) ct,student_id

     from student_enrollment
     group by student_id
     order by ct desc) X

where rownum=1;

Kind regards

    robert

> 
> 
> 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:37:59 CDT