Click here to Skip to main content
15,175,525 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all,


Select ctc from emp e1 where (N-1)=(select count(distinct ctc) from emp where e1.CTC <ctc)>


The above query is for Nth highest salary in table.

I am not clear of logic behind that query.Can any one explain above query in detail.Thanks in advance.
Posted
Updated 7-Nov-13 4:45am
v3

it works - badly - by looking at the ctc value fro each row and comparing it with the number of values which are greater than it. So what it does for each row is examine the entire table in the inner select and compare each value...nasty, slow and somewhat stupid.

A better solution would be to order the data by salary and then just get the fifth item:
SQL
WITH myTableWithRows AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY emp.ctc)) as row,*
    FROM emp)
SELECT * FROM myTableWithRows WHERE row = 5
You may want to add a GROUP BY clause in there to restrict the salaries just just the distinct values.
   
Comments
Thava Rajan 9-Nov-13 8:57am
   
if there are two same salary the result will not be right one
OriginalGriff 9-Nov-13 9:17am
   
That's why I said "You may want to add a GROUP BY clause in there to restrict the salaries just just the distinct values."
Thava Rajan 9-Nov-13 17:20pm
   
can you please provide a answer for such data with employee details
SQL
SELECT * /*This is the outer query part */
FROM Employee Emp1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)
   
well it is a corelated sub query
just try this the result set will give you a better understand

SQL
SELECT  E1.Salary,
    ( /* Corelated Subquery  */
    SELECT COUNT(DISTINCT(E2.Salary))
    FROM Employee E2
    WHERE E2.Salary > E1.Salary
    )as salcount1,
    ( /* Corelated Subquery  */
    SELECT COUNT(DISTINCT(E2.Salary))
    FROM Employee E2
    WHERE E2.Salary < E1.Salary
    ) as salcount2
FROM Employee E1
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900