SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP WHERE SAL

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 / masthan

4 Answers
8302 Views
I also Faced
E-Mail Answers

Answer Posted / masthan

SELECT MAX(SAL) FROM
EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP))

Is This Answer Correct ?

3 Yes

2 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

How many aggregate functions are available there in sql?

952

What is sp_helptext?

1027

What do you mean by “trigger” in sql?

1035

When do we use triggers?

1043

What is the difference between microsoft access and sql?

980

What is pl sql commands?

1007

What is dialect in sql?

902

How do I run a script in sql developer?

914

What are sql constraints?

1151

Can we use commit inside a trigger?

1048

Why do we need sharding?

955

Can we use view in stored procedure?

872

How to add, remove, modify users using sql?

1001

Can a key be both primary and foreign?

960

Can we enter data in a table in design view?

927