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
- 6709 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 is the difference between sum and count in sql?
what is the bond code in materialized view?
How many types of triggers are there in pl sql?
What is sql*loader and what is it used for? : aql loader
How do I create a sql script?
What are all different types of collation sensitivity?
ERROR:Insert or update on table"accnt" violates foreign key constraints "acct_to_curr_symbol" DETAILS:KEY(accnt_curr_id)(-2)is not present in the table "curr_symbol" ......solve The Problem..
How do I save the results of sql query in a file?
What do you mean by rowid?
What is over () in sql?
Which function is used to return remainder in a division operator in sql?
What is the example of procedure?
what are sequences
Is primary key a clustered index?
How many types of tables are there?
