• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

solve this sql sub query

 
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Bartender
Posts: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 598
26
Oracle Notepad Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you, can you tell me how to do with  "correlated subquery" ?
 
Brian Tkatch
Bartender
Posts: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic