The select statement below, only works if the top 3 salaries are unique.
If two (or more) of the top salaried people have similar salaries - this
query breaks down (for the intended result).
As long as the target column is unique, this is an "great" way to limit
selected rows...
Thanks...
-----Original Message-----
From: Mark Thornber [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 14, 2001 12:34 AM
Subject: Re: Limiting the number of records selected.
Try to find a copy of 'SQL for Smarties' by Joe Celko.
Chapter 25 Subsets has example code for the Top N values which I have
successfully used in the past to get Next N as well.
The final iteration of the code to display top three salaries (taken from
the book) is :-
SELECT DISTINCT COUNT(*), a.salary
FROM employees AS a, employees AS b
WHERE (a.salary <= b.salary)
GROUP BY a.salary
HAVING COUNT(*) <=3;
(I did have to translate the SQL into the local version :-^)
In general what is being done is find the number of rows 'less than' the
target row and use that to order and then select the required target rows.
