Re: How to do this Query
Art S. Kagel wrote in message <34563487.7F2D@bloomberg.com>...
>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
A slight offshoot of this that does not require a repetitive delete is to
perform the insert into a temp table with a serial column on it. Then
select the Nth row based on the serial value. Something like this:
create table fred
( dept_id int,
total int,
serial_id serial)
insert into fred
select dept_id, count( emp_id ) empcount
from employees
group by 1
order by 2;
select dept_id from fred
where serial_id = N;
Jay Buckler