The Problem description
Finding the Nth highest salary of workers, where N might be 2, 3, 4, or anything, is one of the most typical SQL interview questions. This query is sometimes rephrased as "find the nth minimal wage in SQL." Many programmers struggle to design the SQL query when the Interviewer keeps asking for the 4th highest, 5th highest, and so on since they only know the easiest approach to address this problem, like by utilizing the SQL IN clause, which doesn't scale well.
You'll need to understand several fundamental concepts like linked subqueries, window functions, and so on in order to tackle this issue efficiently. You can handle all of the variants on your own after you understand the general rationale for solving this problem.
Today, Let's see the four solutions to address this problem in this post, including using the correlated subquery, ROW NUMBER(), TOP in SQL SERVER, and the LIMIT keyword in MySQL.
Creating Data to work on
Before we can solve this problem, we'll need some example data to help us visualize it. Let's make an employee table with some data.
The following query will be used.
CREATE TABLE Employee (name varchar(80), salary int(50));
Now, Insert some data into this table. I have inserted some data as below. Have included some famous names for some excitement :p Hope you guys like cricket!
Solutions to find Nth highest salary in a table using SQL
Here are different ways to calculate the Nth highest salary using plain SQL in different databases like Microsoft SQL Server, MySQL, Oracle, and PostgreSQL
1. Using Correlated Subquery
The linked or correlated subquery is one of the most typical techniques to tackle the challenge of determining the Nth highest wage from the Employee table. This is a sort of subquery that is dependent on the main query and executes for each row returned by the main query.
It's sluggish, yet it can solve issues that would otherwise be difficult to solve. Let's look at the SQL query that uses the Correlated subquery to find the Nth highest income or salary form Employee table.
SELECT * FROM Employee tb1 WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM Employee tb2 WHERE tb2.salary > tb1.salary)
Now, to see the query in action and view results on our database, let's try it out. We need to replace 'N' with the number of highest salary we want. So, let's say we want the 3rd highest salary, we will replace N with 3 here. the query would look like below :
SELECT * FROM Employee tb1 WHERE 3-1 = (SELECT COUNT(DISTINCT salary) FROM Employee tb2 WHERE tb2.salary > tb1.salary)
The result of the above query would be as below :
To deal with duplicate wages in the table, a distinct keyword is used. Only unique wages are considered for determining the Nth highest salary.
The highest salary indicates that no other income is higher than it, the second-highest indicates that just one salary is higher than it, the third-highest indicates that two salaries are higher than it, and the Nth highest salary indicates that N-1 salaries are higher than it.
2. Using TOP keyword (on SQL Server)
To find the Nth highest salary in SQL Server, you can use the TOP keyword. This is also faster than the prior technique since we're not using a subquery to calculate the Nth maximum salary.
The query for the same would be as below :
SELECT TOP 1
FROM (SELECT DISTINCT TOP N salary FROM Employee ORDER BY salary DESC)
AS TEMP_TABLE ORDER BY salary;
Finding the 2nd highest salary with the help of the above query will yield the below results :
3. Using LIMIT keyword (works in MySQL and PostgreSQL)
MySQL, like TOP, has a LIMIT keyword that allows you to paginate your results. Without utilizing a subquery, you may find the nth highest salary in MySQL, as illustrated below.
SELECT * FROM Employee ORDER BY salary DESC LIMIT N-1,1;
Using this query, we can find the 4th highest salary in our example database/table and see how it works.
SELECT * FROM Employee ORDER BY salary DESC LIMIT 4-1,1;
This strategy has the advantage of being faster than a correlated query approach, but it is vendor-dependent. Only a MySQL database will work with this solution.
4. Using ROW_NUMBER() function
SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) rn FROM Employee e ) WHERE rn = N;
There is an issue with the code above. It isn't appropriately managing duplicate wages. For example, in our table, we have two workers with a salary of 3,000, which is our fourth-highest pay; yet, the above code would display the same value, although for a different employee, for both the fourth and fifth-highest salaries.
You may also use SQL statements in Oracle to create schema and execute sample SQL.
In Oracle, you can achieve the same thing using the RANK() window function, but that's a topic for another day. This is more than enough to answer the SQL interview question about the Oracle employee with the nth highest compensation.
No comments:
Post a Comment