How To Find The Second Largest Number in SQL?

Out of the box, most database management systems available today come with built in functions like MAX or MIN. These functions are very common and most of you might have used them at least once while learning SQL or daily at their work. Max is short for Maximum that displays the largest value from a table and Min is the reverse of MAX which displays the minimum value from a table.

Have you ever wondered on how to find the second highest or the second smallest record from a table? Sadly, there are no functions built for that. With this blog, I am going to show you how you can find the Nth largest or the Nth smallest record from a table. The concepts to find Nth largest/smallest is same for all DBMS but the syntax might be little different but I will cover the difference as well.

As a bonus, I will also show you how this can be done in Oracle, MySQL along with Sql Server. To get this started, we need a table with salary field. I am going to re-use the emp_test table  which I created for testing some syntax. This emp_test table has 14 records inserted for our demo. Below is the screen shot of all the records from the emp_test table.

select * from Emp_Test;

Output:

MAX and MIN:
The table has 14 records to experiment. First, we will find MAX/ MIN value and then move on to find the second largest and second smallest number from the table. Finding Max and Min Salary from the table is something every should know it. If you don't, let's do a quick refresh on MAX and MIN before we move on to the real problem we are trying to solve. Finding Max and Min from our Emp_Test Table.

Syntax:

2nd Largest Value:
Next, lets work on to find the second higest salary from emp_test table in Sql Server.

SELECT MAX(sal) as Second_Largest
  FROM emp_test
 WHERE sal < ( SELECT MAX(sal)
                 FROM emp_test);

Output:

Nth Largest & Nth Smallest:
The  above SQL script is easy to understand as it is very straight forward logic. Replacing MAX with MIN gives you the second smallest number from the table. What if someone ask you to display Nth largest or Nth smallest? How are you going to tackle it? Here, I am going to show you how you can achieve this on major databases
ORACLE:

select * from (
  select Emp.*,
row_number() over (order by Salary DESC) rownumb
from Employee Emp
)
where rownumb = n;  /*n is nth highest salary*/

MySQL:

SELECT   salary 
FROM     employee 
ORDER BY salary DESC limit n-1, 
         1

If you followed this blog well, you should be able to find the Nth smallest record from a table. Comment below if you have any questions or topics you will like read next on this blog.