OraFAQ Forum: SQL & PL/SQL » how to find nth maximum salary from a table

Home » SQL & PL/SQL » SQL & PL/SQL » how to find nth maximum salary from a table

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

how to find nth maximum salary from a table [message #8654]

Wed, 17 September 2003 04:28

NS Jagan
Messages: 6
Registered: September 2003

Junior Member

5th maximum salary
9th maximum salary etc

Report message to a moderator

Re: how to find nth maximum salary from a table [message #8674 is a reply to message #8654]

Thu, 18 September 2003 04:19

R.Satish Kumar
Messages: 14
Registered: August 2003

Junior Member

Hi,
Here is ur query.

select max(sal) from emp
where level = &no connect by prior sal > sal

Happy querrying..

Satish.R

Report message to a moderator

Re: how to find nth maximum salary from a table [message #8680 is a reply to message #8654]

Thu, 18 September 2003 06:50

search_before_asking
Messages: 1
Registered: September 2003

Junior Member

Did you know that there is also a search function in this forum?

In case it would be to complex to search you could also use something like:

select *
from ( select a.*, rownum rnum
from ( YOUR_QUERY ) a
where rownum <= :MAX_ROWS )
where rnum >= :MIN_ROW_TO_FETCH
/

Report message to a moderator

Re: how to find nth maximum salary from a table [message #334212 is a reply to message #8654]

Tue, 15 July 2008 21:04

Neo06
Messages: 11
Registered: January 2008

Junior Member

5th max salary...

select min(sal) from
(select distinct sal from emp order by sal desc)
where rownum<=5

Might be posted elsewhere.. anyway wanted to share what I learned.. Smile

Report message to a moderator

Re: how to find nth maximum salary from a table [message #334213 is a reply to message #8654]

Tue, 15 July 2008 21:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated above

>Might be posted elsewhere
Just a few hundred times on this forum.

>.. anyway wanted to share what I learned..
Are you going to share with us every time you learn new SQL?
Please spare us similar unsolicited & needless details.

Report message to a moderator

Re: how to find nth maximum salary from a table [message #334238 is a reply to message #334212]

Tue, 15 July 2008 23:31

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Nice to see you want to share with us what you learn.
For the next time, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

I advice to search for "top n" and you will see many other useful answers.

Regards
Michel

Report message to a moderator

Re: how to find nth maximum salary from a table [message #334247 is a reply to message #8654]

Tue, 15 July 2008 23:56

wangfans
Messages: 7
Registered: July 2008
Location: PRC

Junior Member

user rank()over, you can easily get it

Report message to a moderator

Re: how to find nth maximum salary from a table [message #334254 is a reply to message #334247]

Wed, 16 July 2008 00:10

wangfans
Messages: 7
Registered: July 2008
Location: PRC

Junior Member

SQL> select * from test
2 ;

COL1 COL2
---------- --------------------
22 FF
24 DD
19 WANGFAN
1 a
3 a
5 a
6 b
7 b
8 c
9 c

10 rows selected.

SQL> select * from(select col1,col2 , rank()over(order by col1)rn from test) where rn=2;

COL1 COL2 RN
---------- -------------------- ----------
3 a 2

SQL> select * from(select col1,col2 , rank()over(order by col1)rn from test) where rn=9;

COL1 COL2 RN
---------- -------------------- ----------
22 FF 9

Report message to a moderator