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 you handle exceptions. Give the syntax for it?

1 Answers   BCL, Microsoft,

Types of cursor locks and explanation each of them ?

3 Answers   DELL, HP Finsoft,

How do you get all records from 2 tables. Which join do you use?

8 Answers   Microsoft,

Can instead of triggers be used to fire once for each statement on a view?

0 Answers  

Can we use delete in merge statement?

0 Answers  

Is there a 64 bit version of ssms?

0 Answers  

What is pl sql collection?

0 Answers  

Why are aggregate functions called so?

0 Answers  

How do you remove duplicate records from a table?

0 Answers  

How subquery works in sql?

0 Answers  

What are the benefits of pl sql?

0 Answers  

How do I run a query in pl sql developer?

0 Answers