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

Which is faster count (*) or count 1?

1009

what are the advantages of using stored procedures? : Sql dba

1091

Where can I learn sql for free?

977

What is normalisation and its types?

988

What has stored procedures in sql?

1046

Is it mandatory for the primary key to be given a value when a new record is inserted?

1187

What are the different ddl commands in sql?

1133

What is the difference between inner join and natural join?

990

When sql appeared?

1070

What is sp_helptext?

1051

Which one is better sql or oracle?

949

What is the current version of sql?

1004

What steps server process has to take to execute an update statement?

963

Is sql database free?

959

Explain what is a column in a table?

1084