How to retrieve the 2nd highest salary?

Question

3.00/5 (2 votes)

See more:

This is the 2nd time I encountered this same question in an offline closed-door interview test to retrieve the 2nd highest salary.

These are SQL commands to create table and populate it.

SQL

CREATE TABLE [dbo].[Employee](
	[Name] [nchar](10) NOT NULL,
	[Salary] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO Employee ([Name], [Salary]) VALUES ('Peter', 5000);
INSERT INTO Employee ([Name], [Salary]) VALUES ('Penny', 6000);
INSERT INTO Employee ([Name], [Salary]) VALUES ('Jackson', 7000);
INSERT INTO Employee ([Name], [Salary]) VALUES ('Jenny', 8000);

What I have tried:

My inner query selects the top 2 ordered by descending. The outer query selects the top 1 ordered by ascending. This is how I get the 2nd highest salary. I was wondering if there is a more elegant way of doing it.

SQL

SELECT TOP(1) [Name], [Salary] FROM (
SELECT TOP (2) [Name], [Salary]
  FROM [Test].[dbo].[Employee]
ORDER BY [Salary] DESC
) AS Emp
ORDER BY [Salary];

The output is correct as follows

Name	  Salary
Jackson   7000

Is there a better way of doing this query?

Posted 26-Jul-20 22:40pm

Shao Voon Wong

Updated 26-Jul-20 23:06pm

Add a Solution

2 solutions

Solution 1

As I told you with your last question: How to display result when count=0 for SQL inner join?[^]

Quote:
We are more than willing to help those that are stuck: but that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.
And interview questions are homework on steroids: it's even less fair for us to answer them as if disadvantages people who can do the job you have applied for.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

If you can't do this simple task, you really aren't ready to get this job and would fail the interview badly if you did get through this stage.
Good luck with the job hunt, but you need to know the subject before you apply, not after!

Posted 26-Jul-20 22:55pm

OriginalGriff

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)

Maciej Los · Accepted Answer · 2020-07-26T23:06:00

Solution 2

You need to use ranking function: RANK (Transact-SQL) - SQL Server | Microsoft Docs[^], which returns the rank of each row within the partition of a result set.

Imagine, for the result set of: {5000, 6000, 7000, 7000, 8000} there's 2 rows (records) with the rank of two.

SQL

SELECT *
FROM (
    SELECT [Name], Salary, RANK() OVER(ORDER BY Salary DESC) As RankBySalary 
    FROM Employee
) T
WHERE T.RankBySalary=2

Posted 26-Jul-20 23:06pm

Maciej Los

Updated 26-Jul-20 23:09pm

v2

Comments

Shao Voon Wong 27-Jul-20 5:15am

Thanks. It worked better than my solution because when there are 2 rows with same salary, it displayed both.

Thanks again.

Maciej Los 27-Jul-20 5:17am

You're very welcome.

Sandeep Mewara 27-Jul-20 5:45am

+5 :thumbsup:

Maciej Los 27-Jul-20 5:53am

Thank you, Sandeep.

Fernando_Costa 27-Jul-20 7:55am

Nice tip!!

Maciej Los 27-Jul-20 8:00am

Thanks!