How to select the N highest MySQL record

Dear readers of our blog, we'd like to recommend you to visit the main page of our website, where you can learn about our product SQLS*Plus and its advantages.

SQLS*Plus - best SQL Server command line reporting and automation tool! SQLS*Plus is several orders of magnitude better than SQL Server sqlcmd and osql command line tools.

REQUEST COMPLIMENTARY SQLS*PLUS LICENCE

Enteros UpBeat offers a patented database performance management SaaS platform. It proactively identifies root causes of complex revenue-impacting database performance issues across a growing number of RDBMS, NoSQL, and deep/machine learning database platforms. We support Oracle, SQL Server, IBM DB2, MongoDB, Casandra, MySQL, Amazon Aurora, and other database systems.

23 September 2020

Preamble

SQLS*Plus - 1649772106389ACC7138A 99BA 49C4 B4CA C18D125A02F1 optimize

It is easy to select the highest or lowest entry in the database table using the MAX or MIN function. However, it is a bit difficult to select the n highest record. For example, to get the second most expensive product from the product table.

To select the n highest entry, you have to follow these steps:

First, you get the n highest record and sort them in ascending order. N- I high is the last record in the result set.
Then you sort the result set in descending order and get the first one.

Below is a request to get the n highest entries in ascending order:

SELECT
*
FROM
my_table_name
ORDER BY my_column_name ASC
LIMIT N;

The request to get the n highest record looks like this:

SELECT
*
FROM
(SELECT
*
FROM
my_table_name
ORDER BY my_column_name ASC
LIMIT N) AS tbl
ORDER BY my_column_name DESC
LIMIT 1;

Fortunately, MySQL provides us with the LIMIT offer, which limits the number of lines in the result set to be returned. You can rewrite the query above as the next query:

SELECT
*
FROM
my_table_name
ORDER BY my_column_name DESC
LIMIT n - 1, 1;

The query returns the first line after n-1 lines, so you get the n-th highest record.

Example of how to get the n highest record

For example, if you want to get the second most expensive product (n = 2) in my_products table, you use the following query:

SELECT
productCode, productName, buyPrice
FROM
my_products
ORDER BY buyPrice DESC
LIMIT 1 , 1;

Here is the result:

How to select the N highest MySQL record

The second way to get the n-th highest record is to use subqueries in MySQL:

SELECT *
FROM my_table_name ASa
WHERE n - 1 = (
SELECT COUNT(primary_key_column)
FROMmy_productsb
WHERE b.my_column_name > a. my_column_name)

Using the first method, you can get the same result to get the second most expensive product, use the following query:

SELECT
productCode, productName, buyPrice
FROM
my_products a
WHERE
1 = (SELECT
COUNT(productCode)
FROM
my_products b
WHERE
b.buyPrice > a.buyPrice);

In this article, we have shown how to select an n-database table entry using the LIMIT sentence in MySQL.

Enteros

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of RDBMS, NoSQL, and machine learning database platforms.