Code:
SELECT MIN(T1.yourcolumn)
FROM yourtable T1
INNER JOIN yourtable T2 ON T1.yourcolumn <= T2.yourcolumn
GROUP BY T1.yourcolumn
HAVING count(*) <= 5
This returns the 5th highest value of your column.
I think.
The idea here is to take each value of <yourcolumn> and build a group consisting of a set of other <yourcolumn> values that are less than or equal to it. Those groups which have 5 or fewer values in them are the ones we want, and taking the minimum of those groups gives us the 5th highest value.
Take the MIN aggregate off, and you get a list of the 5 highest values - very handy when you want, say, the top 5 salaries, or whatever...
Since you posted this in the SQL Server forum, this could also be done with a subquery using the (nonstandard) TOP clause, as, e.g.
Code:
SELECT MAX(T1.yourcolumn)
FROM (SELECT TOP 5 yourcolumn FROM yourtable
GROUP BY yourcolumn ORDER BY yourcolumn) T1
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Fetching rows
