Second highest in the table

There are some questions on the exam to verify your knowledge about finding the second highest value of the table or column. It is easy to find the highest value, you just need to sort data using ORDER BY DESC, but don’t forget that ordering data using DESC by default brings NULLs up to the top. So, if your data in the column contains NULL values, you will get NULLs at the top. In order to avoid it use ORDER BY DESC NULLS LAST.

Now, when data was sorted inside the column using ORDER BY DESC NULLS LAST and the highest value is on the TOP, how to select the next value only? In other words, how to select the second highest value?

Let’s consider a real use case How to show the names of products whose list price is the second highest in the table?

As we can see, the innermost subquery has returned the maximum value of the list_price. Next step was a WHERE clause, it has selected all values which are less than the maximum value. And the last step is –  we are selecting the maximum from this list, which is obviously the second highest in the table because the highest was already filtered by WHERE clause.

Pay attention to all these MAX() function calls and comparison operators. There are multiple questions like that one on the exam.

Leave a comment

Your email address will not be published. Required fields are marked *