Posted by Roy Harvey on 12/25/06 13:38

On Sat, 23 Dec 2006 18:17:54 -0500, Serge Rielau <srielau@ca.ibm.com>
wrote:

>Give me all employees of the 5 best paid managers?
>This query is very easy to write with a nested TOP/ORDER BY.
>
>SELECT * FROM EMP
> WHERE MGRID IN (SELECT TOP 5 EMPID FROM MGR
> ORDER BY SALARY DESC)
>
>It gets really nasty without. Matter of fact I can't think of a way
>without cheating (e.g. using ROW_NUMBER() to sneak in the ORDER BY
>through the back door).

If you allow WITH DUPS instead of arbitrarily dropping one:

SELECT *
FROM EMP
WHERE MGRID IN
(SELECT EMPID FROM MGR as M1
WHERE (SELECT count(*) from MGR as M2
WHERE M1.SALARY <= M2.SALARY) <= 5)

Roy Harvey
Beacon Falls, CT

[Back to original message]