retrieve nth salary thru optimized sql query

[kri shan]

EMP table has colum names name and salary. I would lile to retrive nth salary. How can i retrieve nth salary thru optimized sql query ?

[Paul Sturrock]

I would lile to retrive nth salary

Define this. Do you mean the record in record N in the table, regardless of ordering?

[kri shan]

For example, i would like to retrieve 3rd maximum salary. How can i do thru sql query ?

[Paul Sturrock]

Well in Oracle you could do something like this:

SELECT * FROM (
   SELECT salary, ROWNUM RN FROM emp WHERE ROWNUM < 4 ORDER BY salary DESC)
WHERE RN = 3;

Of course ROWNUM is Oracle specific. I suppose you could rewrite the query for other platforms as required. But an easier way is to just query for everything and use the ResultSets absolute(int row) method. Or iterate through the ResultSet and break at row 3. So long as you have ordered the query, and there are 3 or more records in that table it should work.
[ July 28, 2004: Message edited by: Paul Sturrock ]