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

What is cursor in pl sql?

1118

Is coalesce faster than isnull?

966

Will truncate release space?

951

How does rowid help in running a query faster?

1416

How to run sql statements through the web interface?

1041

What does the base_object_type column shows in the user.triggers data dictionary view?

1052

What is the process of debugging?

1070

What is view explain with example?

931

How can we store rows in PL/SQL using array?

1220

Why is theta join required?

1145

What are all the common sql functions?

1030

What does plv msg allows you to do?

1142

What is data modelling in sql?

1019

how to dump a table to a file with 'mysqldump'? : Sql dba

1035

Is merge a dml statement?

979