How do I get the nth highest value in a MySQL result set? Resolving cases using MySQL limit

one of the toughest problems in MySQL is how to get the first highest value in the result set n , such as querying for the second (or third n ) expensive product, and obviously not using a function like Max or min to query for it. However, we can use MySQL LIMIT to solve this problem.

• first , the result set is sorted in descending order.

• The second step is to use LIMIT the clause to obtain n the first expensive product.

The general query is as follows:

SELECT     column1, Column2,... From    TableORDER by Column1 Desclimit nth-1, Count;

Let's take a look at an example where products the structure of the 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 tables:

Mysql> SELECT ProductCode, ProductName, Buypricefrom productsorder 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 mission is to find the product with the second highest price in the result set. You can use LIMIT clauses to select the second row, such as the following query (note: The offset 0 starts from, so you want to specify starting from 1 , then fetch a row of records):

SELECT ProductCode, ProductName, Buyprice from  productsorder by Buyprice Desclimit 1, 1;

Execute the above query statement to get the following results-

Mysql> SELECT ProductCode, ProductName, Buyprice from  productsorder by Buyprice Desclimit 1, 1;+-------------+--- -----------------------------+----------+| ProductCode | ProductName                    | buyprice |+-------------+--------------------------------+----------+| s18_2238    | 1998 Chrysler Plymouth Prowler | 101.51   |+-------------+--------------------------------+-------- --+1 row in Set

Similarly, get the third-highest and fourth-highest product information for: LIMIT 2, 1 and LIMIT 3, 1 .

Related articles:

How to get the nth record in each group of MySQL

MSSQL implementation MySQL limit SQL statement

Related videos:

limit Usage-boolean education swallow 18 MySQL Introductory video tutorial