how to find 2nd highest pay

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

I have a table with name
tbPay

Now there are alot of pays with differnet amount
Now i want to get the 2nd highest pay amount only.

Posted 20-Jun-12 22:03pm

prince_rumeel

Add a Solution

6 solutions

Solution 4

Try the following query.
The salary is the column which contains the employees salary and the tbPay is the name of the table which has the records of the employees.

SQL

SELECT MAX(salary) FROM tbPay WHERE salary NOT IN (SELECT MAX(salary) FROM tbPay)

Does this help?

Posted 20-Jun-12 22:35pm

Pankaj Nikam

Comments

Pankaj Nikam 21-Jun-12 6:11am

Its strange when people downvote and dont specify the reasons for that.

Manas Bhardwaj 21-Jun-12 9:01am

Welcome to the world of UniVoters. This is very usual but commnunity will negate to -ive votes if your answer is correct.
In this case, it is and you get my 5 :)

Pankaj Nikam 21-Jun-12 9:24am

Thanks a lot Manas for your +5 :)
It is really sad to see such things giving a -ve votes without a reason. If I get a reason, its a chance to improve. A -ve vote without a reason lands me in total confusion.

Mohamed Mitwalli 21-Jun-12 9:26am

5+

Pankaj Nikam 21-Jun-12 9:28am

Thanks a lot Mohamed :)

Solution 5

use this query it will return second highest Salary

SQL

select max(salary) FROM tbPay where (salary<(select max(salary) FROM tbPay))

Posted 20-Jun-12 22:57pm

Prosan

Updated 21-Jun-12 3:04am

v2

Comments

Manas Bhardwaj 21-Jun-12 9:03am

Almost correct except with ordering and closing of brackets. I fixed it.

Solution 6

you can try this way

select top 1 esal from EMP where ESAL in (select top 2 ESAL from EMP order by ESAL desc) order by ESAL asc

In the subquery you just changed the top postion like top 3 esal from EMP , you can get the 3rd highest salary.

Posted 21-Jun-12 4:16am

Kamalkant(kk)

Updated 21-Jun-12 4:20am

v2

Solution 7

Hi,

Try this below query to get the second highest PayAmount

SQL

select PayAmount from (
select *,ROW_NUMBER()over(order by PayAmount desc)High from tbpay
)a where High=2

Posted 27-Jun-12 0:10am

kirthiga S

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)

Manas Bhardwaj · Accepted Answer · 2012-06-20T22:33:00

Solution 1

Look at this article: http://blog.sqlauthority.com/2008/04/02/sql-server-find-nth-highest-salary-of-employee-query-to-retrieve-the-nth-maximum-value/[^]

SQL

SELECT
    TOP 1 salary
FROM
    (SELECT DISTINCT TOP 2 salary FROM tbPay ORDER BY salary DESC) a ORDER BY salary

Posted 20-Jun-12 22:33pm

Manas Bhardwaj

Comments

Prasad_Kulkarni 21-Jun-12 4:44am

My 5!

Manas Bhardwaj 21-Jun-12 4:49am

thx!

Mohamed Mitwalli 21-Jun-12 9:24am

5+

OriginalGriff · Accepted Answer · 2012-06-20T22:33:00

Solution 2

Try:

SQL

WITH Results as
   (SELECT Row_Number() over (ORDER BY Salary DESC) as RN,* FROM tbPay)
SELECT * FROM Results WHERE RN=2

Posted 20-Jun-12 22:33pm

OriginalGriff

Comments

dharan1990 25-Jun-12 6:13am

what if two person has same salary as The highest salary

dharan1990 25-Jun-12 6:22am

I think instead of using Row_Number() it is better to use dense_rank() ....Is it correct?