Home » SQL & PL/SQL » SQL & PL/SQL » Find out nth highest salary from Employee Table?
Find out nth highest salary from Employee Table? [message #343732] Thu, 28 August 2008 00:00 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Pls explain the logic behind the query attached in detail.

[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 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Find out nth highest salary from Employee Table? [message #343848 is a reply to message #343732] Thu, 28 August 2008 06:05 Go to previous message
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.
Previous Topic: Sqls
Next Topic: sql output (merged)
Goto Forum:
  


Current Time: Fri Dec 09 21:08:29 CST 2016

Total time taken to generate the page: 0.10399 seconds