What is the Querry for retrieving the 2nd highest salary in a table?

  • Interview Candidate
  • 117
  • 32548

Editorial / Best Answer

muralir02  

  • Member Since Mar-2006 | Mar 20th, 2006

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

Showing Answers 1 - 75 of 117 Answers

Culver_lake

  • Mar 18th, 2006
 

SELECT SALARY

FROM    EMPLOYEE

WHERE  SALARY=(SELECT MAX(SALARY)

                         FROM    EMPLOYEE

                         WHERE  SALARY <> (SELECT MAX(SALARY)

                                                      FROM    EMPLOYEE))

gomathi.e

  • Mar 20th, 2006
 

select max(salary) from <t.name> where salary!=(select max(salary) from <t.name>)

satish

  • Mar 23rd, 2006
 

SELECT MAX(E1.salary)  FROM ggg E1 , ggg E2
WHERE E1.salary< E2.salary

2.select top 1 salary  from ggg where salary<(select max(salary) from ggg)
order by  salary  desc

3.name   salary
rahul   15000
ronak   20000
saumil  25000

create table ggg (name varchar(20),salary int)

insert into ggg values ('saumil',25000)

select * from ggg

Select MAX(Salary) from ggg
Select MAX(Salary) from ggg where Salary <> (Select MAX(Salary) from ggg)

select top 1 salary  from ggg where salary<(select max(salary) from ggg)
order by  salary  desc

SELECT MAX(E1.salary)  FROM ggg E1 , ggg E2
WHERE E1.salary< E2.salary

  Was this answer useful?  Yes

Culver_lake

  • Mar 24th, 2006
 

doesn't it appear from the volume of code in your solution that it is not optimal. It's clearly like using a sledge hammer to crush a walnut. In the real world no one would bear the overhead of this solution when the others on the page do the same thing without creating tables and certainly without that last join step.

SQL is a non-procedural language, your solution is completely procedural.

  Was this answer useful?  Yes

tapas

  • Mar 29th, 2006
 

select * from (select rownum rn,x.* from(select * from emp)x)where rn=2;

Culver_lake

  • Mar 29th, 2006
 

Tapas

I don't think that will work because your query is based on the row number and not the value of the salary column. It's the 2nd highest salary, not the row with the 2nd highest rownumber.

In general for a relational database queries involving physical aspects of the table (like rownum) should be avoided.  The relational model is based on comparing values that represent facts about things in the real world, not facts about the internal makeup of tables rows and columns.

  Was this answer useful?  Yes

srinivas

  • Mar 30th, 2006
 

Hi  u r telling very good answer.

Thanks

Srinivas

  Was this answer useful?  Yes

Culver_lake

  • Mar 30th, 2006
 

sorry most of these solutions do not work as expected. Look at the first one I posted.  that correctly returns the second highest salary. Most of the others are of the form

select salary

from    emp

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

This is wrong. run the query and you will see this returns every salary that's less than the max.  it does not return the second highest salary alone. You have to create an intermediate result that contains every salary except the max salary. finding the max salary in this intermediate result produces  the correct answer. Just like in my solution #1.

  Was this answer useful?  Yes

pawan ahuja

  • Apr 11th, 2006
 

from top analysis query.

select sal from (select sal from emp order by sal desc)

where rownum<3

minus

select sal from (select sal from emp order by sal desc)

where rownum<2

Regards

Pawan Ahuja

  Was this answer useful?  Yes

vijaya

  • Jun 3rd, 2006
 

Hey

This is working perfectly..

viajya

  Was this answer useful?  Yes

sudharson

  • Jun 28th, 2006
 

What is the Query for retrieving the Nth highest  and Nth lowest salary?

  Was this answer useful?  Yes

ganne

  • Jul 31st, 2006
 

select * from(select rownum rno,ename,sal from (select * from emp order by sal desc)) where rno=&m;

  Was this answer useful?  Yes

suryashetty

  • Sep 9th, 2006
 

for 2 nd heighest salary

SELECT TOP1* from(select top2* from tablename orderby salary desc) as table alias name

  Was this answer useful?  Yes

kathir.s

  • Jan 6th, 2007
 

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

  Was this answer useful?  Yes

chayan

  • Jan 13th, 2007
 

it shud be like this

Select max(sal),emp_id

from Employees

group by emp_id

having max(sal) Not In (Select * from employees);

  Was this answer useful?  Yes

Sreejith

  • Feb 28th, 2007
 



Select Top 1 * from Customers where Cust_Salary Not IN (Select Top 1 Cust_Salary from Customers) order by Cust_Salary Desc

  Was this answer useful?  Yes

the_xxx

  • Mar 27th, 2007
 

Hey Guys this should work fine !!

Select Top 1 salary from emp where salary Not IN(select Top 1 salary from emp order by salary Desc)order by salary Desc

Regards
xxx

  Was this answer useful?  Yes

shikha yadava

  • Apr 4th, 2007
 

select max(salary) from tablename A where 2=(select count (salary) from tablename B where B.salary >= A.salary)

  Was this answer useful?  Yes

ajaykumar

  • Apr 13th, 2007
 

select sal from emp e where &n=(select count(sal) from (select distinct sal from emp) where e.sal<=sal);

  Was this answer useful?  Yes

madhug56

  • Jun 6th, 2007
 

To retrieve 2nd highest salary from a table, I think this will work out

select *
from emp a
where 2=
                (select count(distinct(b.sal))
                  from emp b
                  where a.sal <=b.sal);

  Was this answer useful?  Yes

Amrit

  • Jun 13th, 2007
 

select salary from table order by salary desc LIMIT 1,1

  Was this answer useful?  Yes

Ashish Mishra

  • Jun 28th, 2007
 

There are multiple ways to find the same...
I am writing two of them..

 select sal
 from (select rownum ron,b.sal
          from ( select sal
                   from  mysal a
                  order by sal desc) b
        )   
  where ron=2

Or you can use this query ..
select a.*
from mysal a
WHERE ( select count(1)
            from mysal b
      WHERE b.sal >a.sal ) =2  -- Third higghest salary...

You can use connect by etc also.

  Was this answer useful?  Yes

Punitha

  • Jun 29th, 2007
 

you have to use internel query to retreive second highest salary. see the query

select max(a.sal) from emp a, emp b where a.sal<b.sal;

  Was this answer useful?  Yes

nsk

  • Jun 29th, 2007
 

Hi
  My simple query is

select max(salary) from table t where salary not in(select max(salary) from table t)

thanks

  Was this answer useful?  Yes

Gopinadh

  • Jul 3rd, 2007
 

Hi This Query also works fine.

for Nth Heieght

SELECT * FROM(

SELECT ROWNUM RNO,EMPE_ID FROM EMPLOYEES49470 ORDER BY EMPE_ID DESC

) WHERE RNO=&N;


for Nth Lowest

SELECT * FROM(

SELECT ROWNUM RNO,EMPE_ID FROM EMPLOYEES49470 ORDER BY EMPE_ID ASC

) WHERE RNO=&N;

  Was this answer useful?  Yes

sanjay garg

  • Jul 11th, 2007
 

To select nth highest we can use this query,
select column name from table name order by (default/desc) limit(n-1,1);

suppose if you want to find out 4 th highest salary from employee table where 100 of records are there then write like
select salary from employee order by desc limit(3,1);

Amol Pawar

  • Jul 16th, 2007
 

SELECT max(Salary)FROM  Emp WHERE Salary <select MAX(Salary) from Emp 

Try  this, hopes it is beneficial for you

  Was this answer useful?  Yes

vvs

  • Jul 18th, 2007
 

This is my Querry to find Second Maximum salary ...... Select * from emp where sal<(Select max(sal) from emp)....whether the statement is correct or not ...plz reply to me

  Was this answer useful?  Yes

pankaj pandey

  • Jul 19th, 2007
 

select max (sal) from emp
where sal NOT IN (select max(sal) from emp);

  Was this answer useful?  Yes

kowmudiswarna

  • Sep 14th, 2007
 

Assume that table name is emp and ename, sal are columns of the table emp.


Here is the sol to display name and salary for the second highest paid employee.


Select ename, sal from emp where sal=(select min(sal) from(select sal from emp
order by sal desc) where rownum<3)

  Was this answer useful?  Yes

chigs0708

  • May 14th, 2008
 

select v.sal from (select rownum rn,sal from (select distinct s.sal from salary s order by s.sal desc))v where v.rn=2

  Was this answer useful?  Yes

sinhasaurabh014

  • Sep 4th, 2008
 

select * from (select * from(select * from emp order by sal desc) where rownum <=5 order by rownum desc) where rownum =1

Replace the
"where rownum <=5" with <=2 or the number that you want, for eg. if you want 2nd highest its <=2

if you want 3rd highest its <=3 and so on.

  Was this answer useful?  Yes

hdpatel188

  • May 5th, 2010
 

Following query will work if you want to find the second highest value ::


select * from (select rownum rn, x.* from (select * from emp)x)where rn=2;

  Was this answer useful?  Yes

b.ganeshna

  • May 8th, 2010
 

The following query gives the optimized solution for retriving 2'nd highest salary from emp table

SELECT level, max(sal)
FROM emp
WHERE level=2  
connect by prior sal>sal
GROUP BY level;

  Was this answer useful?  Yes

dj_dj_dj

  • May 11th, 2010
 

Select max(salary)
from table_name
where salary <> (select max(salary)
                             from table_name)



Regards
Dharmendra Jaiswal.

  Was this answer useful?  Yes

g.sreemayee

  • May 19th, 2010
 

select e.* from emp e where 2= (select count(distinct(sal)) from emp b where e.sal<=b.sal)


This is a general query where you can select nth number of highest salary by changing the value of n which is 2 here.

  Was this answer useful?  Yes

shyam.dw

  • Jul 24th, 2010
 

SELECT sal_amt FROM
(SELECT salary2.*, rownum rnum FROM
(SELECT sal_amt FROM salary ORDER BY sal_amt DESC)
WHERE rownum<=2)
WHERE rnum>=2;

  Was this answer useful?  Yes

Jitsin

  • Jan 11th, 2011
 

Select * from TABLE_NAME where sal=(select min(sal) from (select sal from TABLE_NAME where rownum<n order by sal desc));


replace n and TABLE_NAME by appropriate values

  Was this answer useful?  Yes

Aditya Panday

  • Jun 8th, 2011
 

SELECT        TOP (1) salary
FROM            (SELECT DISTINCT TOP (2) salary
                          FROM            Employee AS Emp
                          ORDER BY salary DESC) AS derivedEmp
ORDER BY salary

  Was this answer useful?  Yes

uday

  • Aug 9th, 2011
 

To retrieve second highest record, below is working.

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

  Was this answer useful?  Yes

sanath

  • Aug 29th, 2011
 

SELECT MIN(ID) FROM TEST WHERE ID IN
(
SELECT TOP 2 ID FROM TEST order by ID desc
)

from this query u can find 2nd ,3rd so on.. by replacing top 2 to 3,4.....

  Was this answer useful?  Yes

karthickrfwin

  • Sep 22nd, 2011
 

--Use this Simple Script for find second max Salary from Sql Table

Select Top 1 * from TableName
Where Salary < (Select (Max(Salary)) from TableName) Order by Salary desc

  Was this answer useful?  Yes

swati jain

  • Sep 23rd, 2011
 

Select max(empsal) from emp where empsal<(select max(empsal) from emp);

Code
  1.  

Shoaib

  • Dec 21st, 2011
 

select max (sal) from emp
where sal NOT IN (select max(sal) from emp)


This works perfectly :-)

  Was this answer useful?  Yes

m jagadeeswar

  • Dec 30th, 2011
 

Code
  1.  

  Was this answer useful?  Yes

pritesh2010

  • Feb 14th, 2012
 

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
where n > 1 (n is always greater than one).
this will
Find Nth Highest Salary of Employee

  Was this answer useful?  Yes

srivathson

  • Feb 20th, 2012
 

Code
  1.  

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions