How to find nth Highest salary in SQL

Question

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 7-Nov-13 3:43am

DileepReddyC

Updated 7-Nov-13 3:45am

v3

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2013-11-07T03:53:00

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.

coded007 · Answer 2 · 2013-11-08T18:41:00

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)

Posted 8-Nov-13 18:41pm

coded007

Thava Rajan · Answer 3 · 2013-11-09T02:55:00

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

Rajat-Indiandotnet · Answer 4 · 2013-11-10T01:16:00

Solution 4

Hi,
Please refer below link will give 5 different ideas

http://indiandotnet.wordpress.com/2009/03/25/5-ways-in-5-minutes-to-find-2nd-highest-salary-or-2nd-minimum-salary/[^]

Posted 10-Nov-13 1:16am

Rajat-Indiandotnet

How to find nth Highest salary in SQL

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0