solve this sql sub query

[T devillers]

Hello guys ,

These are given column names
"  EMPNO" , "ENAME ", " JOB", " MANAGER"," HIREDATE"  ,"SALARY",  "COMMISSION",   "DEPTNO"  name under "EMPLOYEE' table , i have to find first three maximum salary ?? Please assume oracle db . I know how to find max salary but not first 3 max sal .

Regards;

TS

[Brian Tkatch]

T devillers wrote:These are given column names
"  EMPNO" , "ENAME ", " JOB", " MANAGER"," HIREDATE"  ,"SALARY",  "COMMISSION",   "DEPTNO"  name under "EMPLOYEE' table , i have to find first three maximum salary ?? Please assume oracle db . I know how to find max salary but not first 3 max sal .

To get max salary, the easiest query, as i am sure you know, uses MAX(). To get more than one, one easy way is to use ORDER BY, while returning ROWNUM. Then a second query limits based on ROWNUM. (You can also use ROW_NUMBER() OVER(ORDER BY).)

To start you off, try something like this:
WITH Ordered AS (query returning rownum and salary, with an order by clause) SELECT query retuning Salary, limiting it to three records.

[T devillers]

To start you off, try something like this:
WITH Ordered AS (query returning rownum and salary, with an order by clause) SELECT query retuning Salary, limiting it to three records.

I never heard about such query . I was hoping to try something like  Co- Related Sub Queries . For example below query is giving me only 3rd max salary . But i want all 3 max salary in one single sub query .
Select * From emp x Where 3 = (Select Count(Distinct Sal)                     From emp                    Where x.sal<= sal);                    

[Brian Tkatch]

You never used a CTE? No problem, you can do the same thing with a query in the FROM clause too.

SELECT Salary FROM (query returning rownum aliased as RN and Salary, with an order by clause) WHERE RN <= 3.
The trick here is to have the subquery get the records in order and to number them. The outer query simply limits based on that number.

[T devillers]

Thank you, can you tell me how to do with  "correlated subquery" ?

[Brian Tkatch]

T devillers wrote:Thank you, can you tell me how to do with  "correlated subquery" ?

I found this using a google search. The first approach is correlated.