Interested to Buy Any Domain ? << Click Here >> for more details...
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
- 8619 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
What are stored procedures in mysql?
What is the usage of sql functions?
Explain foreign key in sql?
how to calculate expressions with sql statements? : Sql dba
Can a commit statement be executed as part of a trigger?
What plvcmt and plvrb does in pl/sql?
How do you update f as m and m as f from the below table testtable?
what's the difference between a primary key and a unique key? : Sql dba
explain commit and rollback in mysql : sql dba
How you improve the performance of sql*loader? : aql loader
What are the different ways to optimize a sql query?
what are the type of locks ? : Sql dba
What is the requirement of self-join?
How do I run a pl sql program?
What are the differences between implicit and explicit cursors?
