MariaDB Tutorial / Sort and Paginate Results with ORDER BY and LIMIT

ORDER BY defines a result’s order, and LIMIT restricts how many rows are returned and where to start. When sort values tie, add a unique secondary key to make top lists and page boundaries deterministic for an unchanged data set.

Specify both ordering and the result size

The three most expensive products require a descending price order as well as a three-row limit. LIMIT 3 alone does not establish which three products should be first. Sorting describes the result order rather than physically rearranging stored rows. Use Filter Rows with WHERE, AND, OR, IN, BETWEEN, and LIKE to select the input rows before ordering. Improve Date Filters, Sorting, and Pagination Without Changing Results extends the same criteria to preserving result meaning while examining deep-page access costs.

Each sort expression has its own direction. In ORDER BY price DESC, product_id ASC, DESC does not apply to both columns. A numeric-looking string also sorts according to its own type and comparison rules.

Check the MariaDB documentation for the syntax and conditions used here: LIMIT, order by.

Prepare rows with deliberate price ties

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating products_17_lab. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

USE shopcourse;
CREATE TABLE products_17_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p17_price CHECK (price >= 0),
  CONSTRAINT ck_p17_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_17_lab
  (product_id, product_name, category, price, stock, note)
VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

The SQL retains the original Korean sample strings to exercise UTF-8 consistently. Product identifiers 1–6 represent a notebook, pen, mug, lamp, folder and cable; their category strings mean stationery, household and electronics. Enter the string literals as shown, use the identifiers when comparing results, and save the file with UTF-8 encoding.

IDs 3 and 6 both cost 8000, and IDs 2 and 5 both cost 1500. These ties make secondary ordering observable. product_id uniquely identifies every row and is a suitable final tie-breaker in this fixture.

Add a unique tie-breaker to the price order

SELECT product_id, product_name, price FROM products_17_lab
ORDER BY price DESC, product_id ASC LIMIT 3;

MariaDB compares price first, then product_id when the prices are equal. Change only the ID direction to DESC to put ID 6 ahead of ID 3, then restore ASC. A data set with no ties today can acquire them later; omitting a tie-breaker because current examples look stable is not a lasting ordering rule.

Understand OFFSET and changing page boundaries

SELECT product_id, price FROM products_17_lab
ORDER BY price DESC, product_id ASC LIMIT 2 OFFSET 0;
SELECT product_id, price FROM products_17_lab
ORDER BY price DESC, product_id ASC LIMIT 2 OFFSET 2;

The first page returns IDs 4 and 3; the second returns 6 and 1. OFFSET counts skipped rows, not pages. With a page size of two, the third page starts at offset four. LIMIT 2, 2 expresses the second query too, but the explicit LIMIT count OFFSET position form makes the roles easier to review.

A tie-breaker does not freeze data between requests. Concurrent inserts, deletions or price changes can move page boundaries. Large offsets may also require substantial work on earlier rows; a small returned result does not guarantee that sorting and filtering touched only that many rows.

Use the last sort values and choose NULL placement

If the previous page ends at price 8000 and ID 3, the next rows have a lower price, or the same price and a larger ID. The cursor condition must match both sort directions:

SELECT product_id, price FROM products_17_lab
WHERE price < 8000 OR (price = 8000 AND product_id > 3)
ORDER BY price DESC, product_id ASC LIMIT 2;

IDs 6 and 1 match the OFFSET example. This is keyset pagination: preserve the last ordering values for the next request. It has different requirements from jumping directly to an arbitrary page number. A completely fixed list during concurrent writes needs an additional consistency policy.

SELECT product_id, note FROM products_17_lab
ORDER BY note IS NULL ASC, note ASC, product_id ASC;

NULL sorts as a low value in an ordinary ascending order. The explicit Boolean expression places non-NULL notes first, then sorts their note values and IDs. Collation determines the detailed text order; focus on the explicit NULL policy and tie-breaker.

Diagnose unexpected ordering and constrain user options

Remove LIMIT temporarily and display the primary key with all sort expressions. Check directions, aliases, types and the unique tie-breaker; an index’s apparent order is not an implicit ORDER BY. If an application lets users choose sorting, map only allowed columns and directions. Value placeholders do not generally parameterize arbitrary identifier names. Validate page sizes as bounded integers.

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

DROP TABLE shopcourse.products_17_lab;