RE: How to do this Query
I tried to send this directly to Mr. Black but it bounced so I will
post here.
Art S. Kagel, kagel@bloomberg.com
On Tue, 28 Oct 1997, Scott Black wrote:
}
}
} > -----Original Message-----
} > From: Art S. Kagel [SMTP:kagel@bloomberg.com]
} > 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);
} [Scott] It's not quite that hard. You can combine query one
} and two and get
} select count(*) num_of_emp, dept_id
} from emp_table
} group by dept_id
} having count(*) = 1 #or having count(*) = N
Oh no another example of Kagel's First Law of SQL! Seriously, this is not
what Sathish wanted to see. Your department returns the dept with one
employee or the dept with a particular number of employees. Sashish wanted
the Nth largest department by number of employees. You absolutely cannot
do this directly in a single SQL statement. He wants the equivalent of:
Select dept_id, count(*)
from employee
order by 2
group by 1 | head -4 | tail -1
Cool, too bad we cannot do stuff like this.
Art S. Kagel, kagel@bloomberg.com