OraFAQ Forum: SQL & PL/SQL » Find out nth highest salary from Employee Table?

Home » SQL & PL/SQL » SQL & PL/SQL » Find out nth highest salary from Employee Table?

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

Find out nth highest salary from Employee Table? [message #343732]

Thu, 28 August 2008 00:00

ygsunilkumar
Messages: 100
Registered: August 2008

Senior Member

select distinct (a.salary) from employees a
where &N = (select count (distinct(b.salary))
from employees b where a.salary <=b.salary);

Hello! Please any one explain me how exactly the above query will execute. I would like to know step by step logic behind it.

[Updated on: Thu, 28 August 2008 00:01]

Report message to a moderator

Re: Find out nth highest salary from Employee Table? [message #343734 is a reply to message #343732]

Thu, 28 August 2008 00:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

I am sorry to see that both GOOGLE & the Search function on this forum are broken for you.
Repairs are under way.
Please wait patiently
Additional information will be available after repairs are complete.

[Updated on: Thu, 28 August 2008 00:05] by Moderator

Report message to a moderator

Find out nth highest salary from Employee Table? [message #343736 is a reply to message #343732]

Thu, 28 August 2008 00:09

ygsunilkumar
Messages: 100
Registered: August 2008

Senior Member

Pls explain the logic behind the query attached in detail.

Attachment: nth highest salary.txt
(Size: 0.13KB, Downloaded 651 times)

[Updated on: Thu, 28 August 2008 00:51]

Report message to a moderator

Re: Find out nth highest salary from Employee Table? [message #343743 is a reply to message #343736]

Thu, 28 August 2008 00:50

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can answer this by yourself by answering both:
- What is the meaning of "select count (distinct(b.salary))
from employees b where a.salary <=b.salary"?
- What is the meaning of "nth highest salary"?

Regards
Michel

Report message to a moderator

Re: Find out nth highest salary from Employee Table? [message #343848 is a reply to message #343732]

Thu, 28 August 2008 06:05

aparangi_rhl
Messages: 10
Registered: January 2006

Junior Member

In co-related queries, for each row in the outer table (in your case TABLE a), the inner query (in your case TABLE b)is executed as many times as there are rows in the inner query checking the 'where' clause for true/false.

Try to take a small sample of the table and execute it on paper for each row. This might help you understand how the query works.

Report message to a moderator

Previous Topic:	Sqls
Next Topic:	sql output (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 13:57:51 CST 2024