Home » SQL & PL/SQL » SQL & PL/SQL » how to write this select statement
how to write this select statement [message #18464] Thu, 31 January 2002 18:22 Go to next message
Girish
Messages: 16
Registered: September 1998
Junior Member
Hello,

Lets assume we have a table like this

EMP_ID SALARY
A01 3500
A02 4500
A03 3000
A04 4000
A05 3000
A06 5000
A07 4000
A08 5500
A09 6000
A10 6000

If I want to retrieve the nth highest salary how should I write the select statement. For example if I wish to retrieve the 4th highest salary, it should give me 4500.

Thank you!!!
Re: how to write this select statement [message #18467 is a reply to message #18464] Thu, 31 January 2002 22:52 Go to previous messageGo to next message
Milind S Deobhankar
Messages: 33
Registered: January 2002
Member
select salary from( select rownum id, salary from(select distinct salary from employee order by salary desc)
)where id=4

This will give the fourth highest salary if you want other, then put id= your desirable number.
Re: how to write this select statement [message #18471 is a reply to message #18464] Fri, 01 February 2002 00:08 Go to previous messageGo to next message
Mike
Messages: 417
Registered: September 1998
Senior Member
From Oracle 8.1.6 or 8.1.7 on you got a new function which is very performant to do such things.

SELECT sal
FROM
( SELECT sal,
ROW_NUMBER() OVER (ORDER BY sal) s_rank
FROM emp
)
WHERE s_rank=4

Depending how you define the ranking you can also use RANK() or DENSE_RANK() instead of ROW_NUMBER() . The difference between RANK and DENSE_RANK is that DENSE_RANK leaves no gaps in ranking sequence when there are ties. That is, if you were ranking a competition using DENSE_RANK and had three people tie for second place, you would say that all three were in second place and that the next person came in third. The RANK function would also give three people in second place, but the next person would be in fifth place.

HTH
Mike
Re: how to write this select statement [message #18474 is a reply to message #18467] Fri, 01 February 2002 00:43 Go to previous message
Girish
Messages: 16
Registered: September 1998
Junior Member
Milind,

Thank you very much.

Regards
Girish Mayachari :-)
Previous Topic: nth record from a sorted output of a table
Next Topic: SQL%NOTFOUND
Goto Forum:
  


Current Time: Sat Apr 04 22:28:05 CDT 2020