SELECT * FROM employees ORDER BY salary DESC LIMIT 2 , 1 its so sim

how to check the 3rd max salary from an employee table? One
of the queries used is as follows:

select sal from emp a where 3=(select
count(distinct(sal)) from emp b
where a.sal<=b.sal).

Here in the sub query "select
count(distinct(sal)) from emp b
where a.sal<=b.sal" or
"select count(distinct(sal)) from emp b
where a.sal=b.sal" should reveal the same number of rows is
in't it? Can any one here please explain me how is this
query working perfectly.

However, there is another query to get the 3rd highest of
salaries of employees that logic I can understand. Pls find
the query below.
"select min(salary) from emp where salary in(select
distinct
top 3 salary from
emp order by salary desc)"

Please explain me how

"select sal from emp a where 3=(select
count(distinct(sal)) from emp b
where a.sal<=b.sal)" works

source:http://www.allinterview.com/showanswers/33264.html.

Thanks in advance
Regards,
Karthik.

Question Posted / aalee

4 Answers
8616 Views
I also Faced
E-Mail Answers

Answer Posted / aalee

SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 2 , 1

its so simple!!!!!!

Is This Answer Correct ?

6 Yes

8 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

How can use stored procedures in sql?

1190

What is difference between stored procedures and application procedures?

1064

i have some prob lem to tell me about my self in interview first round ...

2215

What is output spooling in sql*plus?

1090

How do I order by ascending in sql?

1078

What are the different types of dbmss?

1054

What is sql analyzer?

1059

what is a trigger in mysql? : Sql dba

1214

What is dynamic query?

1034

How can I see all tables in sql?

1142

Can a primary key be a foreign key?

1079

Is sql a programming?

1026

How many scalar data types are supported in pl/sql?

1103

How to avoid using cursors?

1175

How show all rows in sql?

1057