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'

what is view? : Sql dba

0 Answers  

Why does sql need a server?

0 Answers  

use of IN/ANY/ALL

5 Answers   Ramco,

What is dba in sql? : SQL DBA

0 Answers  

Is sqlite good enough for production?

0 Answers  

What is use of term?

0 Answers  

What is the execution plan in sql?

0 Answers  

what is row? : Sql dba

0 Answers  

what are date and time functions in mysql? : Sql dba

0 Answers  

what is a table called, if it has neither cluster nor non-cluster index? What is it used for? : Sql dba

0 Answers  

type type_name is table of varchar2(30) index by binary_integer for the above type you have to create a identifier... like identifier_name type_name; for the above type you can use the below methods..like first , last , prior, next , delege..etc...like this.. if you create a cursor...like cursor cursor_name is select * from scott.emp; is there any methods like above to use prior, fist , last , next , trim ,etc...

1 Answers   Satyam,

What is the importance of sqlcode and sqlerrm?

0 Answers