How to find Nth Highest Salary in MySQL and SQL Server? Example LeetCode Solution

Nth Highest Salary in MySQL and SQL Server - LeetCode Solution
---------------------------------------------------------------
Write a SQL query to get the nth highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

1. Accepted Solution

This was my accepted solution for this LeetCode problem:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
 
BEGIN
DECLARE M INT;
SET M = N - 1;
 
RETURN (
 
      # WRITE your MySQL query statement below.    
      SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1    
  );
END

2. Alternate Solution

This is another solution to find Nth highest salary problem, this was not accepted by LeetCode compiler but they work fine on Database

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
 
BEGIN
 
  RETURN (
 
      # WRITE your MySQL query statement below.
 
      SELECT Salary FROM Employee a 

             WHERE N = (SELECT COUNT(Salary) 
                        FROM Employee b
                        WHERE a.Salary <= b.Salary)
   
  );
 
END

3. How to create own Employee table for testing?

If you want to test in your local database then you can use the following SQL query to create an Employee table and populate with some sample data. After that, you can run the SQL query to find the Nth highest salary for testing.

CREATE TABLE Employee (
 
  Id INT NOT NULL,
 
  Salary INT NULL
 
  
  );
 
 
 
INSERT INTO Employee VALUES (1, 100);
 
INSERT INTO Employee VALUES (2, 200);
 
INSERT INTO Employee VALUES (3, 300);

4. SQL query to find the Nth highest salary

Here is the SQL query you can use to find the Nth highest salary for the above table, you can run this in your local database and it should return the

SELECT Salary FROM Employee  a 

WHERE N = ( SELECT COUNT(Salary) FROM Employee b WHERE a.Salary <= b.Salary );

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no Nth highest salary, then the query should return null. You can see that we have used the above query to find the highest, second-highest, and third-highest salaries from the employee table. 

Btw, If you are new to SQL and don't understand fundamentals like COUNT() function or co-related sub-queries then I highly recommend you go through comprehensive SQL courses from Coursera and  Udemy. It's one of the best and also most affordable courses to learn SQL online.

By the way, this is my fourth article on solving LeetCode SQL problems, earlier I have solved the following problems if you want you can check them as well:

1. How to find duplicate emails? 
2. How to find customers who have never ordered?
3. How to join two tables using LEFT join?

That's all about how to solve the Nth highest salary problem in MySQL and SQL Server. It's a good problem to learn co-related subquery, one of the tricky SQL concepts which many programmers struggle to understand.  LeetCode also has a good collection of SQL problems which are good to improve your SQL query skills and I suggest you take a look at those problems if you want to improve your SQL query skills.


Other related SQL queries, Interview questions, and articles:

• How to join three tables in one single SQL query (solution)
• 10 Frequently asked SQL Query interview questions (solution)
• Write a SQL query to find all table names on a database in MySQL (solution)
• 5 Courses to learn Database and SQL Better (courses)
• Top 5 Websites to learn SQL online for FREE? (resource)
• 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
• 4 ways to find the Nth highest salary in SQL (solution)
• Difference between Self and Equi Join in SQL? (answer)
• 5 Free Courses to learn Oracle and SQL Server? (courses)
• Top 5 Courses to learn MySQL Database for Beginners (Courses)
• What is the difference between View and Materialized View in Database? (answer)
• Difference between clustered and non-clustered indexes in SQL? (answer)
• 5 Advanced SQL books to level up your SQL skills (books)
• Write a SQL query to copy or backup a table in MySQL (solution)
• Difference between Primary and Candidate key in table? (answer)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• Difference between Unique and Primary key in table? (answer)
• Top 5 Courses to learn PostgreSQL in-depth (courses)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• What is the difference between UNION and UNION ALL in SQL? (answer)
• 4 Free Books to learn Microsoft SQL Server database (books)
• Top 5 Websites to learn SQL online for FREE? (websites)
• How to migrate SQL queries from Oracle to SQL Server 2008? (answer)
• Difference between Primary and Foreign key in table? (answer)
• Top 5 Courses to learn Microsoft SQL Server in-depth (courses)
• How do you find the duplicate rows in a table on a database? (solution)
• The real difference between WHERE and HAVING clause in SQL? (answer)
• 5 Free Courses to learn Database and SQL (free courses)

Thanks for reading this article, if you like this SQL article, then please share it with your friends and colleagues. If you have any questions or feedback, then please drop a note.

P.S. - If you are interested in learning Database and SQL and looking for some free resources to start your journey, then you can also take a look at the Introduction to Databases and SQL Querying free course on Udemy to kick-start your learning.