How we find third, Fourth and Fifth highest salary through SQl queries?

Replies to This Discussion

Permalink Reply by kotla lakshmi kanth reddy on November 6, 2011 at 11:22am

For third maximum salary,consider the emp table

      SELECT max(sal) from emp

      where sal < (select max(sal) from emp

      where sal < (select max(sal) from emp ) )

       In that way you write fourth and fifth max salary for the emp table...

 

 

• ▶ Reply

Permalink Reply by Hitesh Shah on November 6, 2011 at 12:59pm

Hi,

Thanks for reply.

for second highest salary we can write -

SELECT max(sal) from emp

      where sal < (select max(sal) from emp)

Please correct me.

is there any another way to write second, third, fourth and fifth maximum salary ?

Thanks

Hitesh Shah

 

• ▶ Reply

Permalink Reply by prasad on November 7, 2011 at 10:37am

hi hitesh we can use TOP and NOT IN

 

2nd Highest Salary

select max(salary) from Employeedetails
where salary not in (select top 1 salary from Employeedetails order by salary desc )

3nd Highest Salary

select max(salary) from Employeedetails
where salary not in (select top 2 salary from Employeedetails order by salary desc )

4nd Highest Salary

select max(salary) from Employeedetails
where salary not in (select top 3 salary from Employeedetails order by salary desc )

...................

 

• ▶ Reply

Permalink Reply by Hitesh Shah on November 7, 2011 at 7:13pm

Hi,

Really very easy query we can use.

Thanks for it.

Hitesh Shah

• ▶ Reply

Permalink Reply by kotla lakshmi kanth reddy on November 7, 2011 at 12:51pm

Hi prasad ,

                  How we find the nth maximum salary from the emp table ?

• ▶ Reply

Permalink Reply by prasad on November 7, 2011 at 4:07pm

select max(salary) from Employeedetails

• ▶ Reply

Permalink Reply by prasad on November 7, 2011 at 4:13pm

to get n max sal

select top 1 esal from emp order by esal desc

• ▶ Reply

Permalink Reply by Rajesh Sama on November 8, 2011 at 9:15pm

Finding nth highest salary:

select rownum, empno, ename, sal from (select * from emp order by sal desc) group by rownum, empno, ename, sal having rownum='&n';

Note : rownum is the inbuilt field / column, we can't see the rownum field / column in the table. By default it will take the values as 1, 2, 3, .........

• ▶ Reply

Permalink Reply by chandra sekhar on December 5, 2011 at 11:44am

we can write this way also,

select max(sal) from emp where sal< (select top2 sal from emp orderby sal desc)....

This will give the 3rd highest sal....

• ▶ Reply

Permalink Reply by narendra on December 5, 2011 at 11:11pm

With out using top

select a.sal from emp a

where n=(select count(distinct(b.sal) from emp b) where a.sal<b.sal)

if we want 3rd highest n=4,to get "nth" max ,specify a number "n-1" in place of n

• ▶ Reply

Permalink Reply by shruti chaudhary on February 24, 2012 at 6:40pm

select rownum , slary from employee where rownum in (''3','4','5') order by salary desc

This will work for Oracle.

For SQL server rank function will be used

• ▶ Reply

Permalink Reply by ganesh on February 26, 2012 at 7:47am

Hi,

SELECT LEVEL,MAX(SAL) FROM EMP  WHERE LEVEL IN (3,4,5)  CONNECT BY PRIOR SAL > SAL  GROUP BY LEVEL;

Thanks

• ▶ Reply

• ‹ Previous
• 1
• 2
• Next ›
• Page