How to get the nth highest value in a MySQL result set? Solution case using MySQL LIMIT

One of the most difficult problems in MySQL is: how to get the nth highest value in the result set, such as querying the second (or n) most expensive value Which product is it obviously cannot be queried using functions such as MAX or MIN. However, we can use MySQL LIMIT to solve such problems.

• First , sort the result set in descending order.

• The second step is to use the LIMIT clause to obtain the nth most expensive product.

The general query is as follows:

SELECT 
    column1, column2,...
FROM
    table
ORDER BY column1 DESC
LIMIT nth-1, count;

Let’s take a look at an example. The structure of the products table is as follows-

mysql> desc products;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| productCode        | varchar(15)   | NO   | PRI | NULL    |       |
| productName        | varchar(70)   | NO   |     | NULL    |       |
| productLine        | varchar(50)   | NO   | MUL | NULL    |       |
| productScale       | varchar(10)   | NO   |     | NULL    |       |
| productVendor      | varchar(50)   | NO   |     | NULL    |       |
| productDescription | text          | NO   |     | NULL    |       |
| quantityInStock    | smallint(6)   | NO   |     | NULL    |       |
| buyPrice           | decimal(10,2) | NO   |     | NULL    |       |
| MSRP               | decimal(10,2) | NO   |     | NULL    |       |
+--------------------+---------------+------+-----+---------+-------+
9 rows in set

View the row records in the following product table:

mysql> SELECT productCode, productName, buyprice
FROM products
ORDER BY
 buyprice DESC;
+-------------+--------------------------------------+----------+
| productCode | productName                          | buyprice |
+-------------+--------------------------------------+----------+
| S10_4962    | 1962 LanciaA Delta 16V               | 103.42   |
| S18_2238    | 1998 Chrysler Plymouth Prowler       | 101.51   |
| S10_1949    | 1952 Alpine Renault 1300             | 98.58    |
| S24_3856    | 1956 Porsche 356A Coupe              | 98.3     |
| S12_1108    | 2001 Ferrari Enzo                    | 95.59    |
| S12_1099    | 1968 Ford Mustang                    | 95.34    |
... ....
+-------------+--------------------------------------+----------+
110 rows in set

Our task is to find the product with the second highest price in the result set. You can use the LIMIT clause to select the second row, such as the following query (note: the offset starts from 0, so specify starting from 1, and then Take a row of records):

SELECT productCode, productName, buyprice FROM  products
ORDER BY buyprice DESC
LIMIT 1, 1;

Execute the above query statement and get the following results-

mysql> SELECT productCode, productName, buyprice FROM  products
ORDER BY buyprice DESC
LIMIT 1, 1;
+-------------+--------------------------------+----------+
| productCode | productName                    | buyprice |
+-------------+--------------------------------+----------+
| S18_2238    | 1998 Chrysler Plymouth Prowler | 101.51   |
+-------------+--------------------------------+----------+
1 row in set

Similarly, obtain the product information with the third and fourth highest selling prices: LIMIT 2, 1 and LIMIT 3, 1.

Related articles:

How to get the Nth record in each Mysql group

mssql implements mysql limit sql statement

Related videos:

limit usage-Boolean Education Yan Shiba mysql introductory video tutorial

The above is the detailed content of How to get the nth highest value in a MySQL result set? Solution case using MySQL LIMIT. For more information, please follow other related articles on the PHP Chinese website!