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
- 6694 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
Why is pl sql needed?
What is a dirty read sql?
What are the different types of functions in sql?
How delete all records from table in sql?
What is group by in sql?
What are the uses of sysdate and user keywords?
When is the update_statistics command used?
What is the best sql course?
What is package in pl sql with an examples?
What is orm in sql?
What is the use of procedures?
differentiate between float and double. : Sql dba
How do you drop a trigger?
what is a composite primary key ? : Sql dba
Will truncate release space?
