Home » SQL & PL/SQL » SQL & PL/SQL » how to find nth maximum salary from a table
how to find nth maximum salary from a table [message #8654] Wed, 17 September 2003 04:28 Go to next message
NS Jagan
Messages: 6
Registered: September 2003
Junior Member
5th maximum salary
9th maximum salary etc
Re: how to find nth maximum salary from a table [message #8674 is a reply to message #8654] Thu, 18 September 2003 04:19 Go to previous messageGo to next message
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
Re: how to find nth maximum salary from a table [message #8680 is a reply to message #8654] Thu, 18 September 2003 06:50 Go to previous messageGo to next message
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
/
Re: how to find nth maximum salary from a table [message #334212 is a reply to message #8654] Tue, 15 July 2008 21:04 Go to previous messageGo to next message
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
Re: how to find nth maximum salary from a table [message #334213 is a reply to message #8654] Tue, 15 July 2008 21:17 Go to previous messageGo to next message
BlackSwan
Messages: 25039
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.


Re: how to find nth maximum salary from a table [message #334238 is a reply to message #334212] Tue, 15 July 2008 23:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
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
Re: how to find nth maximum salary from a table [message #334247 is a reply to message #8654] Tue, 15 July 2008 23:56 Go to previous messageGo to next message
wangfans
Messages: 7
Registered: July 2008
Location: PRC
Junior Member

user rank()over, you can easily get it
Re: how to find nth maximum salary from a table [message #334254 is a reply to message #334247] Wed, 16 July 2008 00:10 Go to previous messageGo to next message
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
Re: how to find nth maximum salary from a table [message #334266 is a reply to message #334254] Wed, 16 July 2008 00:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
@wangfans,

I asked you before to follow this forum's guidelines for posting code.
Re: how to find nth maximum salary from a table [message #334283 is a reply to message #334266] Wed, 16 July 2008 01:25 Go to previous message
wangfans
Messages: 7
Registered: July 2008
Location: PRC
Junior Member

ok,very sorry
Previous Topic: Rows Data into Columns
Next Topic: Table Joining
Goto Forum:
  


Current Time: Mon Dec 05 19:17:19 CST 2016

Total time taken to generate the page: 0.14270 seconds