Click here to Skip to main content
15,171,873 members
Please Sign up or sign in to vote.
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
Updated 27-Jul-20 0:06am

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

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900