how to find the second highest salary from emp table?
Answers were Sorted based on User's Feedback
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 |
Answer / jat16may
SELECT MAX(SALARY) FROM TABLENAME WHERE SALARY<(SELECT
MAX(SALARY) FROM TABLE NAME
Is This Answer Correct ? | 10 Yes | 0 No |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
how many tables will create when we create table, what are they? : Sql dba
what is row? : Sql dba
Explain the difference between triggers and constraints?
Explain constraints in sql?
what does it mean to have quoted_identifier on? : Sql dba
What are Nested Tables? How will u delete 5 rows from Nested Tables
When are we going to use truncate and delete?
What is meant by Join? What are the different types of Joins available? Explain.
is mysql query is case sensitive? : Sql dba
what is the command line end user interface - mysql? : Sql dba
Does oracle roll back the transaction on an error?
what is meant by nl2br()? : Sql dba