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
- 8470 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
Why do we use triggers?
What is time based sql injection?
What is procedure explain with program?
What is use of term?
What is the use of count (*) in sql?
How do I remove sql developer from windows 10?
How do I run a pl sql program?
what is the difference between primary key and unique key? : Sql dba
Mention what does plvtab enables you to do when you showthe contents of pl/sql tables?
What is the function that is used to transfer a pl/sql table log to a database table?
How do I count rows in sql?
how to fetch alternate records from a table? : Sql dba
explain advantages of innodb over myisam. : Sql dba
What are sql functions? Describe in brief different types of sql functions?
What is set serveroutput on?
