• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

solve this sql sub query

 
T devillers
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:
 
T devillers
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


To start you off, try something like this:


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 .

 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You never used a CTE? No problem, you can do the same thing with a query in the FROM clause too.


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
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you, can you tell me how to do with  "correlated subquery" ?
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic