15,175,525 members
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

## Solution 1

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.
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

## Solution 2

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)```

## Solution 3

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```