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.
- 4 Answers
- 6707 Views
- I also Faced
- E-Mail Answers
Answer Posted / gaurav
If you use "select count(distinct(sal)) from emp b
where a.sal=b.sal" It will always give output as 1. because for every salary in emp a, there will be one distinct sal in emp b.
So when we use a.sal <= b.sal, we are counting the number of people who have salary greater than or equal to your salary. So comparing it with 3 will give us 3rd maximum salary.
| Is This Answer Correct ? | 0 Yes | 0 No |
Post New Answer View All Answers
What is implicit cursor in pl sql?
Why we use sql profiler?
how to see the create table statement of an existing table? : Sql dba
What are the different types of tables in sql?
What is sql stand for?
what are date and time intervals? : Sql dba
What is optimistic concurrency control? : Transact sql
Difference between table function and pipelined function?
How do I tune a sql query?
what are the disadvantages of mysql? : Sql dba
Why does sql need a server?
What is the difference between functions, procedures, and packages in pl/sql?
what are date and time functions in mysql? : Sql dba
how to get a list of columns in an existing table? : Sql dba
What is pl/sql language case sensitive?
