OraFAQ Forum: SQL & PL/SQL » how to write this select statement

Home » SQL & PL/SQL » SQL & PL/SQL » how to write this select statement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to write this select statement [message #18464]

Thu, 31 January 2002 18:22

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!!!

Report message to a moderator

Re: how to write this select statement [message #18467 is a reply to message #18464]

Thu, 31 January 2002 22:52

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.

Report message to a moderator

Re: how to write this select statement [message #18471 is a reply to message #18464]

Fri, 01 February 2002 00:08

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

Report message to a moderator

Re: how to write this select statement [message #18474 is a reply to message #18467]

Fri, 01 February 2002 00:43

Girish
Messages: 16
Registered: September 1998

Junior Member

Milind,

Thank you very much.

Regards
Girish Mayachari :-)

Report message to a moderator

Previous Topic:	nth record from a sorted output of a table
Next Topic:	SQL%NOTFOUND

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 19:56:18 CDT 2024