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.
Answers were Sorted based on User's Feedback
Answer / 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 |
Answer / s.panda
select level,max(sal) from emp where level=&levelno
connect by prior sal>sal group by level
By entering the level no,Accordingly we can get the nth highest salay of a table.
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / 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 |
Answer / aalee
SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 2 , 1
its so simple!!!!!!
Is This Answer Correct ? | 6 Yes | 8 No |
How do I count rows in sql?
What is pl/sql language case sensitive?
What are tables and fields in the database?
How to change the order of columns in Oracle SQL Plus ?
Which command is used to delete a trigger?
what is a table called, if it has neither cluster nor non-cluster index? What is it used for? : Sql dba
counting the no.of characters occurs in a string by using pl/sql function
What does trigger mean in psychology?
When you have to use a default "rollback to" savepoint of plvlog?
How to make a copy values from one column to another in sql?
What is Data Concarency and Consistency?
What is out parameter used for eventhough return statement can also be used in pl/sql?