1. Categories >> Software >> Databases >> SQL PLSQL
  2. Suggest New Category

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.

Question Posted / karthikvenkataram

Answers were Sorted based on User's Feedback



how to check the 3rd max salary from an employee table? One of the queries used is as follows: ..

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

how to check the 3rd max salary from an employee table? One of the queries used is as follows: ..

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

how to check the 3rd max salary from an employee table? One of the queries used is as follows: ..

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

how to check the 3rd max salary from an employee table? One of the queries used is as follows: ..

Answer / aalee

SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 2 , 1


its so simple!!!!!!

Is This Answer Correct ?    6 Yes 8 No

Post New Answer

More SQL PLSQL Interview Questions

How to find only %th Highest Sal

6 Answers   Infogain, Symphony,

Types of optimization?

6 Answers   Microsoft,

What is user define exception and example

1 Answers   HP, KP,

what are the advantages a stored procedure? : Sql dba

0 Answers  

Can we use commit or rollback in trigger? If yes, then how. Please explain with a suitable example?

5 Answers   HSBC,




How can check sql version from command line?

0 Answers  

Can a select statement fire a trigger?

0 Answers  

How would you go about increasing the buffer cache hit ratio? 0. Explain the difference between a hot backup and a cold backup and the benefits associated with each 1. You have just had to restore from backup and do not have any control files. How would you go about bringing up this database? 2. How do you switch from an init.ora file to a spfile? 3. Explain the difference between a data block, an extent and a segment. 4. Give two examples of how you might determine the structure of the table DEPT. 5. Where would you look for errors from the database engine? 6. Compare and contrast TRUNCATE and DELETE for a table. 7. Give the reasoning behind using an index. 8. Give the two types of tables involved in producing a star schema and the type of data they hold. 9. What type of index should you use on a fact table? 10. Give two examples of referential integrity constraints. 11. A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables? 12. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each. 13. What command would you use to create a backup control file? 14. Give the stages of instance startup to a usable state where normal users may access it. 15. What column differentiates the V$ views to the GV$ views and how? 16. How would you go about generating an EXPLAIN plan?

2 Answers  

how to find the second highest salary from emp table?

211 Answers   CIS, Cognizant, Cosmosoft, DAS, EDS, GreenTech, HOV Services, IBM, Infosys, National Institute of Science and Technology, Patni, Persistent, Polaris, TCS, Wipro, Yardi, Zensar,

How to create a view on a table which does not exists

4 Answers   Oracle, TCS,

Source is Sales Table: Sno Prod Sales Sales_Amount 1 A 10 2000 2 A 20 1000 3 C 10 3000 4 D 30 4000 5 A 20 1000 Target : Sales_Count T_Sales_Amt Sales_Count(A) T_Sales_Amt(A) 90 11000 50 4000 In single query, pls tell me.

5 Answers   CGI,

Give an example of any procedure.

5 Answers   Accenture, iFlex, Wipro,

For more SQL PLSQL Interview Questions Click Here
Categories