How to get third highest salary from employee table when multiple record exist with third highest salary

Question

0.00/5 (No votes)

See more:

Hi,

I have employee table with Id,Name and salary.

1 E1 10,000
2 E2 20,000
3 E3 30,000
4 E4 20,000
5 E5 50,000

I need third highest all record. What should be my query when there are multiple record with third highest salary. 20,000 is third highest salary and it is twice in database.

Posted 19-Oct-15 21:01pm

Sharma Ravi

Add a Solution

Comments

Kornfeld Eliyahu Peter 20-Oct-15 3:20am

Look for SQL's ranking functions...

2 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 · Accepted Answer · 2015-10-19T21:20:00

Solution 1

Try:

SQL

SELECT Id, Name, Salary 
FROM (
    SELECT Id, Name, Salary, ROW_NUMBER() OVER (ORDER BY Salary, Id) AS RowNum
    FROM MyTable   
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum = 3

Posted 19-Oct-15 21:20pm

OriginalGriff

Comments

Sharma Ravi 20-Oct-15 3:30am

It is showing only single record as result but result should display both E2 and E4 because both are third highest.

jaket-cp · Accepted Answer · 2015-10-19T23:36:00

Solution 2

I believe what you are asking is to get the third highest salary and then re-query the employee table and get all the employees with the same third highest salary.

If that is the case, here is one way of doing it:

SQL

select 
	* 
from employee e
inner join (
	select salary
	from employee
	order by salary desc
	offset 3 rows fetch next 1 rows only
) ThirdHighest
	on e.salary = ThirdHighest.salary
;

The main point is, is getting the third highest salary, which can be done in many different ways.
This example uses the OFFSET FETCH Clause, which is a SQL Server 2012+ feature.

Posted 19-Oct-15 23:36pm

jaket-cp

Comments

Sharma Ravi 23-Oct-15 2:44am

Can you give me some more example to do this?

jaket-cp 23-Oct-15 4:04am

If you are asking about getting the third highest...
Solution 1 has an example.
You could do something like this
select top 1 salary from (select top 3 salary from employee order by salary desc) order by salary.
Not tested, but you should get logic behind it.
have a look at
http://blog.sqlauthority.com/2007/04/27/sql-server-query-to-retrieve-the-nth-maximum-value/

I am quite sure there are other ways of doing it too.
Do a google search of
"tsql select nth record"
http://lmgtfy.com/?q=tsql+select+nth+record
should get you more examples.

Hope that helps out.