nth highest value

we can extract the nth highest salary in the following way in sql server 2000.

say, sal is the field in emp table.

select top 1 q2.sal from
(select top "your N goes here" sal from
(select distinct(sal) from emp ) q1
order by sal desc) q2
order by q2.sal asc

put the value of N as 1 or 2 or 3 to get 1st highest, 2nd highest or 3rd highest value of salary respectively.

we use distinct key word, 'cause many people may have same salary. but the query is not modified for N value greater than the no. of records in the table. want a suggestion for that modification.

Mridul Buragohain.
Greatech Soft Solutions.
New Delhi, India

Geez, I've seen this question alot, and they always use the "salary" from an "employee" table

Anyway, I replied in the Oracle forum w/an approach that uses T-SQL, take a look. All you'd have to do is supply an if statement to first do a count(distinct salary) on the table, and compare it to the "nth" value desired, if "n" is < the count, then proceed, else throw an error of somekind.

http://forums.devshed.com/t187260/s...e1a915636d.html