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

Can we use ddl statements in stored procedure?

1154

How can check sql version from command line?

1088

What is pl sql in dbms?

951

Why do we use %rowtype & %type in plsql?

1051

What is having clause in sql?

966

Can a key be both primary and foreign?

996

Why we use join in sql?

978

what is a stored procedure? : Sql dba

1133

What is the purpose of cursors in pl/sql?

1137

What is set transaction?

1037

How does an execution block start and end in pl sql?

951

What is a system versioned table?

987

how can you create an empty table from an existing table? : Sql dba

1154

What is the use of triggers?

1038

Which column in the user.triggers data dictionary view shows that the trigger is a pl/sql trigger?

1038