Re: How to do this Query
S.Sathish wrote:
>
> Hi,
> Table : Dept_id Emp_id
>
> I want the dept_id which has Nth maximum number of employees.
>
> Eg: 1st maximum no of employees ie,..,the dept_id which has the max no
> of employees.
> 2nd max no of employees ie,..,the dept_id which is second in the
> list of no of employees in a dept
>
> ..........
>
> So, i want a general method for the Nth case.
This is easiest and most efficient to do in a programming language, just
select ... order by ... group by ... and ignore the first n-1 rows. If
you must use pure SQL try this mess:
select dept_id, count( emp_id ) empcount
from employees
group by 1
into temp fred;
-N-1 times do:
delete from fred
having empcount = max(empcount);
Then
select * from fred
having enpcount = max(empcount);
Art S. Kagel