Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query (n th Salary)
SQL Query (n th Salary) [message #329149] Tue, 24 June 2008 04:58 Go to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Can anyone explain how the below query will work internally in the oracle i am confused .


This is the query to find the n th maximum salary

SELECT DISTINCT SAL FROM EMP A WHERE &N=(SELECT COUNT (DISTINCT B.SAL) 
FROM EMP B WHERE A.SAL<=B.SAL);


when i am try to split the query into two and then i execute this the result will be in below.

SQL> select count(distinct sal) from emp where 5000<=5000;

COUNT(DISTINCTSAL)
------------------
                12

SQL> ed
Wrote file afiedt.buf

  1* select count(distinct sal) from emp where 1300<=1300
SQL> /

COUNT(DISTINCTSAL)
------------------
                12


My question here is how it will take the 2nd maximum salary when i given the n th value as 2.

Values in the table will be

SQL> select sal from emp;

       SAL
----------
       800
      1600
      1250
      2975
      1250
      2850
      2450
      3000
      5000
      1500
      1100

       SAL
----------
       950
      3000
      1300



16 rows selected.
Re: SQL Query (n th Salary) [message #329150 is a reply to message #329149] Tue, 24 June 2008 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't pick up queries on the Web.
Try to answer yourself to the question that are asked.
This query is about 15 years old, it is no more the way to achieve the problem it answered.

Regards
Michel
Re: SQL Query (n th Salary) [message #329157 is a reply to message #329150] Tue, 24 June 2008 05:44 Go to previous message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
ok michel thanks
Previous Topic: Joining of Table A & B
Next Topic: Merge two Queries (Sub Query)?
Goto Forum:
  


Current Time: Thu Dec 08 16:26:05 CST 2016

Total time taken to generate the page: 0.09106 seconds