Answer / atheeq

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

Answer / jat16may

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

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.

Answer / ayush

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

Answer / shanmugasundaram

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

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)

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)

Answer / raghu munukutla

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

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.........

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'

How do I view tables in sql developer?

0 Answers  

What is the difference between view and stored procedure?

0 Answers  

What are the differences between Database Trigger and Integrity constraints ?

4 Answers  

What is the purpose of using pl/sql?

0 Answers  

What is the purpose of primary key?

0 Answers  

What is the difference between UNIQUE KEY and UNIQUE INDEX?

3 Answers   TCS,

What is rename command in sql?

0 Answers  

What are the possible values that can be stored in a boolean data field?

0 Answers  

how to create a new table in mysql? : Sql dba

0 Answers  

What is a table in a database?

0 Answers  

Q1.all the depts which has more then 10 empls? Q2.all the dept which does not have any emply? Q3 all the emp which does not have any dept? Q4 get all the emply detais with the dept details it dept is exit otherwise any emp details? Q5 how to debugg the dynamic sql and packages?

2 Answers  

Is left join inner or outer by default?

0 Answers