Sep 17, 2020 by Robert Gravelle

It's been said that second place is the first loser. So, who needs an SQL statement to find out who these under achievers are? Surprisingly, a lot of people. In fact, the official term for this type of query is "nth highest value of a column". That's because techniques for selecting the 2nd highest value may also be applied for any value. In today's blog, we'll learn how to use ORDER BY DESC in conjunction with the LIMIT clause to obtain the 2nd highest value, and others, from a table.

Introducing the Classic Models Database

The classicmodels database is a MySQL sample database to help learn SQL quickly and effectively. The classicmodels database represents a retailer of scale models of classic cars. It contains typical business data such as customers, products, sales orders, sales order line items, etc.

Here's a peek at the contents of the payments table in Navicat Premium:

We will compose a query that selects the 2nd highest payment from this table.

About the LIMIT Statement

The LIMIT clause may be added to a SELECT statement to constrain the number of rows returned. The LIMIT clause can accept either one or two arguments of zero or positive whole integers.

Here's the syntax:

SELECT 
    select_list
FROM
    table_name
LIMIT [offset,] row_count;

• The offset specifies the offset of the first row to return. The offset of the first row is 0, not 1.
• The row_count specifies the maximum number of rows to return.

Selecting the 2nd Highest Payment

Knowing what we know about the LIMIT clause, we can now structure our SELECT statement as follows to fetch the 2nd highest value:

SELECT * FROM yourTableName ORDER BY DESC yourColumnName LIMIT 1,1;

Here is the equivalent statement to SELECT the 2nd highest amount from the payments table:

Verifying the Results

In Navicat, we can sort a table or view by any column by hovering the mousepointer over the column header and then clicking the context menu arrow. We can then choose the sort order from the list:

If we refer to the highlighted row in the image below, we can confirm that it is the correct one.

Selecting the Nth Highest Payment

We can use the same syntax to fetch other amounts. For example, you could return the fourth highest value of a column by using the following syntax:

SELECT * FROM yourTableName ORDER BY DESC yourColumnName LIMIT 3,1;

In fact, we can use this syntax for any ranking:

SELECT * FROM yourTableName ORDER BY DESC yourColumnName LIMIT desiredRank - 1, 1;

Here's the query to fetch the 10th highest payment amount:

Conclusion

In today's blog, we learned how to use ORDER BY DESC in conjunction with the LIMIT clause to obtain the Nth highest value from a table. In next week's blog, we'll accomplish the same task using the TOP statement.

Interested in Navicat Premium? You can try it for 14 days completely free of charge for evaluation purposes!