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
- 6734 Views
- I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
What is a file delimiter?
Which sql statement is used to delete data from a database?
What are different methods to trace the pl/sql code?
What are stored procedures used for?
where are cookies actually stored on the hard disk? : Sql dba
Mention what problem one might face while writing log information to a data-base table in pl/sql?
what is the command used to fetch first 5 characters of the string? : Sql dba
How do I view a view in sql?
What is sap sql?
What is full join in sql?
Why is partition used in sql?
Explain unique key in sql.
What is a pdo connection?
How to pronounce postgresql?
The in operator may be used if you know the exact value you want to return for at least one of the columns.
