We all know that the MAX function can be used to find the largest value in SQL. How, then, can we write a single-pass SQL that can be used across different database systems to find the second largest value in a column? Single-pass means only one SQL query gets executed, as opposed to having multiple SQL statements using temporary tables to store intermediate results.

Logically, the way to approach this is to first find the largest value (easy to do with MAX), exclude that value from the data set being evaluated, and then find the largest value in the resulting set. In SQL, we use the subquery construct to accomplish this as follows:

SELECT MAX(COLUMN_NAME) FROM TABLE_NAME
WHERE COLUMN_NAME <
(SELECT MAX(COLUMN_NAME) FROM TABLE_NAME);

Please note that the SQL above will find the second largest *distinct* value. Let’s say, for example, if the table consists of the following numbers:

25
10
8
12
25

In this case, the result we get using the above SQL is 12, even though in reality 12 is the 3rd largest number. This is because the top 2 numbers are both 25, and thus both would be excluded in the WHERE clause.