how to find the second highest salary from emp table?

Answers were Sorted based on User's Feedback



how to find the second highest salary from emp table?..

Answer / atheeq

select distinct emp_sal from emp where 1 order by emp_sal
desc limit 1,1

Is This Answer Correct ?    12 Yes 1 No

how to find the second highest salary from emp table?..

Answer / jat16may

SELECT MAX(SALARY) FROM TABLENAME WHERE SALARY<(SELECT
MAX(SALARY) FROM TABLE NAME

Is This Answer Correct ?    10 Yes 0 No

how to find the second highest salary from emp table?..

Answer / susanta

select * from emp e1 where N-1 =
(select count(distinct sal)
from emp e2
where e2.sal > e1.sal)
for Nth highest salary.

Is This Answer Correct ?    20 Yes 11 No

how to find the second highest salary from emp table?..

Answer / ayush

select *
from (select ename,empno,salary,dense_rank()over(order by
salary desc) rank from emp)
where rank =2

Is This Answer Correct ?    19 Yes 11 No

how to find the second highest salary from emp table?..

Answer / shanmugasundaram

select min(salary) from tblempsal where salary in(select
distinct
top 2 salary from
tblempsal order by salary desc)

Is This Answer Correct ?    20 Yes 13 No

how to find the second highest salary from emp table?..

Answer / hitesh pundir

elect max(salary) from m1 where salary not in (select top
n-1 salary from m1 order by salary desc)

-->note where n=number as we want
to display the highest salary.

ex for 2nd highest salary-->
select max(salary) from m1 where salary not in (select top 1
salary from m1 order by salary desc)

ex for 3rd highest salary-->
select max(salary) from m1 where salary not in (select top 2
salary from m1 order by salary desc)

Is This Answer Correct ?    4 Yes 1 No

how to find the second highest salary from emp table?..

Answer / amit singh

forget the book

Empsal table data
700
500
100
900
400
200
600
750


query to find second highest salary from table Empsal

mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary<(select max(salary) from Empsal));

Output=750



query to find third highest salary from table Empsal


mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary<
>(select max(e1.salary) from Empsal e1
>where e1.salary IN(
>select e2.salary from Empsal e2 where
>e2.salary<(select max(salary) from Empsal))));
Output=700

RUN THE QUERY THEN ARG
amitsing2008@gmail.com(amit is back on this site)

Is This Answer Correct ?    3 Yes 0 No

how to find the second highest salary from emp table?..

Answer / raghu munukutla

SQL> select max(sal) from emp where rownum<=(select
count(sal)-1 from emp)
order by sal desc;

Is This Answer Correct ?    3 Yes 0 No

how to find the second highest salary from emp table?..

Answer / sujith

Here is the query to find not only 2nd highest salary, but
nth highest salary also by changing n value......

select e.* from (select * from emp order by sal desc) e
where rownum<=n
minus
select e.* from (select * from emp order by sal desc) e
where rownum<n

This will the record of the person who draws nth highest
salary.........

Is This Answer Correct ?    4 Yes 2 No

how to find the second highest salary from emp table?..

Answer / rajesh

In MS SQL I was able to run the query:
select min(sal) from emp where sal in ( select top 2 sal
from emp order by desc)

but i was not able to run similar query in MySQL 5.1.41


select min(e_sal) from employee where e_sal in (select e_sal
from employee order by e_sal desc limit 2);

MySQL gives the following error:

ERROR 1235 (42000): This version of MySQL doesn't yet
support 'LIMIT & IN/ALL/ANY/SOME subquery'

Is This Answer Correct ?    4 Yes 2 No

Post New Answer

More SQL PLSQL Interview Questions

how many tables will create when we create table, what are they? : Sql dba

0 Answers  


what is row? : Sql dba

0 Answers  


Explain the difference between triggers and constraints?

0 Answers  


Explain constraints in sql?

0 Answers  


what does it mean to have quoted_identifier on? : Sql dba

0 Answers  






What are Nested Tables? How will u delete 5 rows from Nested Tables

3 Answers   IBM,


When are we going to use truncate and delete?

0 Answers  


What is meant by Join? What are the different types of Joins available? Explain.

5 Answers   Cap Gemini,


is mysql query is case sensitive? : Sql dba

0 Answers  


what is the command line end user interface - mysql? : Sql dba

0 Answers  


Does oracle roll back the transaction on an error?

0 Answers  


what is meant by nl2br()? : Sql dba

0 Answers  


Categories