Mastering SQL ORDER BY and LIMIT: Sorting, Pagination, and Best Practices

Overview

Raw data from a database is rarely presented exactly as needed, so we previously demonstrated filtering techniques. In addition to filtering, you may need to sort data—for example, to see the highest‑spending items by ordering the amount field descending, or to view ages in ascending order by sorting the age column.

You might also need to limit data, such as rewarding different gifts to users ranked 1‑10, 11‑20, 21‑30 based on payment amounts.

Note: In the scripts below, brackets [] denote optional parts, and the pipe | separates alternatives.

Data Sorting (ORDER BY)

Syntax:

<code>SELECT ... ORDER BY column_name [ASC|DESC], column2 [ASC|DESC], ...;</code>

1. The field to sort follows the ORDER BY clause.

2. ASC (ascending) and DESC (descending) specify the order; ASC is the default.

3. Multiple fields can be specified, separated by commas.

4. Earlier fields have higher priority; when values are equal, the next field is used.

Single‑Field Sorting

Example: display orders by amount in descending order.

<code>mysql&gt; SELECT * FROM t_order;
+---------+---------+----------+-------+
| orderid | account | amount   | goods |
+---------+---------+----------+-------+
| 8       | brand   | 52.2     | 2     |
| 9       | hen     | 1752.02  | 7     |
| 10      | helyn   | 88.5     | 4     |
| 11      | sol     | 1007.9   | 11    |
| 12      | diny    | 12       | 1     |
| 13      | weng    | 52.2     | 5     |
| 14      | sally   | 99.71    | 9     |
| 15      | brand1  | -99.99   | 5     |
+---------+---------+----------+-------+
7 rows in set

mysql&gt; SELECT * FROM t_order ORDER BY amount DESC;
+---------+---------+----------+-------+
| orderid | account | amount   | goods |
+---------+---------+----------+-------+
| 9       | hen     | 1752.02  | 7     |
| 11      | sol     | 1007.9   | 11    |
| 14      | sally   | 99.71    | 9     |
| 10      | helyn   | 88.5     | 4     |
| 8       | brand   | 52.2     | 2     |
| 13      | weng    | 52.2     | 5     |
| 12      | diny    | 12       | 1     |
+---------+---------+----------+-------+
7 rows in set</code>

Multiple‑Field Sorting

Separate fields with commas; priority follows left‑to‑right. If amounts are equal, goods are sorted next.

<code>mysql&gt; SELECT * FROM t_order ORDER BY amount DESC, goods DESC;
+---------+---------+----------+-------+
| orderid | account | amount   | goods |
+---------+---------+----------+-------+
| 9       | hen     | 1752.02  | 7     |
| 11      | sol     | 1007.9   | 11    |
| 14      | sally   | 99.71    | 9     |
| 10      | helyn   | 88.5     | 4     |
| 13      | weng    | 52.2     | 5     |
| 8       | brand   | 52.2     | 2     |
| 12      | diny    | 12       | 1     |
+---------+---------+----------+-------+
7 rows in set</code>

Sorting by Alias

Aliases simplify queries; they can be English or Chinese.

<code>mysql&gt; SELECT account AS ac, amount AS am, goods AS gd FROM t_order ORDER BY am, gd DESC;
+-------+------+------+
| ac    | am   | gd   |
+-------+------+------+
| diny  | 12   | 1    |
| weng  | 52.2 | 5    |
| brand | 52.2 | 2    |
| helyn | 88.5 | 4    |
| sally | 99.71| 9    |
| sol   |1007.9| 11   |
| hen   |-99.99| 7    |
+-------+------+------+
7 rows in set</code>

Using Functions in Sorting

The ABS() function can be used to sort by absolute value, causing negative numbers to appear after positive ones when sorting descending.

<code>mysql&gt; SELECT * FROM t_order ORDER BY ABS(am) DESC;
+-------+------+--------+
| ac    | am   | gd     |
+-------+------+--------+
| hen   |1752.02| 7    |
| sol   |1007.9 |11    |
| brand1|-99.99| 5    |
| sally | 99.71 | 9    |
| helyn | 88.5  | 4    |
| brand | 52.2  | 2    |
| weng  | 52.2  | 5    |
+-------+------+--------+
8 rows in set</code>

Combining with WHERE

Apply ORDER BY after a WHERE filter to sort the already‑filtered rows.

<code>mysql&gt; SELECT * FROM t_order WHERE amount>80 AND goods>5 ORDER BY amount DESC;
+---------+---------+----------+-------+
| orderid | account | amount   | goods |
+---------+---------+----------+-------+
| 9       | hen     | 1752.02  | 7     |
| 11      | sol     | 1007.9   | 11    |
| 14      | sally   | 99.71    | 9     |
+---------+---------+----------+-------+
3 rows in set</code>

Data Limiting (LIMIT)

After filtering, you may need a specific range of rows, such as rewarding users ranked 1‑10, 11‑20, etc. LIMIT restricts the number of rows returned, useful for ranking or pagination.

<code>SELECT column_list FROM table_name LIMIT [offset,] count;</code>

1. offset – number of rows to skip (default 0). 2. count – number of rows to return after the offset. 3. Neither offset nor count can be expressions.

Getting the First N Records

LIMIT n and LIMIT 0,n are equivalent.

<code>mysql&gt; SELECT * FROM t_order LIMIT 2;
+---------+---------+----------+-------+
| orderid | account | amount   | goods |
+---------+---------+----------+-------+
| 8       | brand   | 52.2     | 2     |
| 9       | hen     | 1752.02  | 7     |
+---------+---------+----------+-------+
2 rows in set

mysql&gt; SELECT * FROM t_order LIMIT 0,2;
+---------+---------+----------+-------+
| orderid | account | amount   | goods |
+---------+---------+----------+-------+
| 8       | brand   | 52.2     | 2     |
| 9       | hen     | 1752.02  | 7     |
+---------+---------+----------+-------+
2 rows in set</code>

Limiting to a Single Record

Combine ORDER BY with LIMIT 1 to fetch the maximum or minimum value.

<code>mysql&gt; SELECT * FROM t_order WHERE amount>0 ORDER BY amount DESC LIMIT 1;
+---------+---------+----------+-------+
| orderid | account | amount   | goods |
+---------+---------+----------+-------+
| 9       | hen     | 1752.02  | 7     |
+---------+---------+----------+-------+
1 row in set

mysql&gt; SELECT * FROM t_order WHERE amount>0 ORDER BY amount ASC LIMIT 1;
+---------+---------+----------+-------+
| orderid | account | amount   | goods |
+---------+---------+----------+-------+
| 12      | diny    | 12       | 1     |
+---------+---------+----------+-------+
1 row in set</code>

Fetching an (m,n) Range

Skip m rows and then return n rows.

<code>mysql&gt; SELECT * FROM t_order ORDER BY amount LIMIT 2,4;
+---------+---------+----------+-------+
| orderid | account | amount   | goods |
+---------+---------+----------+-------+
| 8       | brand   | 52.2     | 2     |
| 13      | weng    | 52.2     | 5     |
| 10      | helyn   | 88.5     | 4     |
| 14      | sally   | 99.71    | 9     |
+---------+---------+----------+-------+
4 rows in set</code>

Pagination typically uses LIMIT pageIndex*pageSize, pageSize . For 31 rows with pageSize=10 :

Page 0: LIMIT 0,10

Page 1: LIMIT 10,10

Page 2: LIMIT 20,10

Page 3: LIMIT 30,10

Important notes:

LIMIT arguments must be explicit numeric values, not expressions.

Both arguments must be non‑negative integers.

If the sorting column contains duplicate values, pagination can produce inconsistent or duplicate rows; add a unique column (e.g., primary key) to the ORDER BY clause.

Example of Duplicate‑Value Sorting Issue

When sorting by age alone, pages may contain overlapping rows because many rows share the same age.

<code>-- First page (offset 0,3)
SELECT * FROM user3 ORDER BY age LIMIT 0,3;
-- Second page (offset 3,3)
SELECT * FROM user3 ORDER BY age LIMIT 3,3;
-- Third page (offset 6,3)
SELECT * FROM user3 ORDER BY age LIMIT 6,3;</code>

Adding the primary key resolves the issue:

<code>SELECT * FROM user3 ORDER BY age, id LIMIT 0,3;
SELECT * FROM user3 ORDER BY age, id LIMIT 3,3;
SELECT * FROM user3 ORDER BY age, id LIMIT 6,3;</code>

Summary

1. ORDER BY column_name [ASC|DESC] sorts query results; ASC is default. 2. LIMIT restricts the number of rows returned, taking two parameters offset and count . offset defaults to 0, both must be non‑negative integers, and cannot be expressions. 3. When paginating, avoid sorting on columns with duplicate values; append a unique column (such as the primary key) to the ORDER BY clause to ensure stable ordering.