Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to obtain the nth highest value in the MySQL result set, which has a certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

One of the thorniest problems in MySQL is how to get the nth highest value in the result set, such as querying which product is the second (or nth) most expensive product, which obviously cannot be obtained by 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 get the nth expensive product.

The general query is as follows:

SELECT column1, column2,...FROM tableORDER BY column1 DESCLIMIT nth-1, count

Let's 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 (10L2) | NO | NULL | MSRP | decimal (10L2) | NO | | | NULL | | +-+-+ 9 rows in set |

View the row records in the following product table:

Mysql > SELECT productCode, productName, buypriceFROM productsORDER BY buyprice DESC +-+ | productCode | productName | buyprice | +-+-- -+-+ | S10room4962 | 1962 LanciaA Delta 16V | 103.42 | | S18room2238 | 1998 Chrysler Plymouth Prowler | 101.51 | | S10room1949 | 1952 Alpine Renault 1300 | 98.58 | S24room3856 | 1956 Porsche 356A Coupe | 98.3 | S12room1108 | 2001 Ferrari Enzo | | 95.59 | | S1251099 | 1968 Ford Mustang | 95.34 |. +-+ 110 rows in set

Our task is to find the second most expensive product in the result set. You can use the LIMIT clause to select the second row, such as the following query (note: the offset starts at 0, so specify 1, and then take a row of records):

SELECT productCode, productName, buyprice FROM productsORDER BY buyprice DESCLIMIT 1, 1

Execute the above query and get the following results-

Mysql > SELECT productCode, productName, buyprice FROM productsORDER BY buyprice DESCLIMIT 1,1 +-+ | productCode | productName | buyprice | +-+- +-+ | S18002238 | 1998 Chrysler Plymouth Prowler | 101.51 | +-+ 1 row in set

Similarly, the third and fourth most expensive products are: LIMIT 2, 1 and LIMIT 3, 1.

Thank you for reading this article carefully. I hope it will be helpful for everyone to share how to get the nth highest value in the MySQL result set. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.