Sorting with ORDER BY and Limiting Results

A query without ORDER BY returns rows in whatever order the database feels like — which is rarely the order you want. Add ORDER BY and the database sorts the results before handing them back. Add LIMIT and it stops after the first N. Together they let you express the verifications testers reach for constantly: the most recent order, the top 5 most expensive products, the largest cart in the last hour. This lesson covers both, plus the pagination escape hatch you'll use to test paginated APIs.

Why result order isn't guaranteed

Without ORDER BY, the database returns rows in no particular order. It might happen to look sorted by id, but that's an accident — the database is allowed to change its mind, and on a busy table it sometimes does. So if your test depends on the order, put it in writing with ORDER BY. Don't rely on luck.

Step 1 of 4

Run base query

Apply FROM and WHERE — get the matching rows in arbitrary order.

ORDER BY — sorting the result

The basic shape is ORDER BY column [ASC | DESC]. ASC means ascending (A→Z, 0→∞ — the default if you don't say). DESC means descending (Z→A, ∞→0).

-- Sort users alphabetically by name
SELECT name, email FROM users ORDER BY name ASC;

Result:

| name        | email             |
|-------------|-------------------|
| Alice Khan  | alice@example.com |
| Bob Patel   | bob@example.com   |
| Carol Singh | carol@example.com |
| Dan Müller  | dan@example.com   |
| Esha Roy    | esha+qa@test.com  |

Sorted ascending by name. The ASC is optional — ORDER BY name does the same thing.

-- Newest orders first
SELECT id, total, status, created_at
FROM orders
ORDER BY created_at DESC;

Result (sample):

| id | total  | status     | created_at          |
|----|--------|------------|---------------------|
| 5  |  25.00 | cancelled  | 2026-05-05 09:11:08 |
| 4  |  63.00 | pending    | 2026-05-03 17:42:30 |
| 3  | 320.00 | shipped    | 2026-05-01 12:08:50 |
| 2  |  48.50 | completed  | 2026-04-28 10:01:14 |
| 1  | 145.00 | completed  | 2026-04-20 14:30:21 |

-- Cheapest products first
SELECT name, price FROM products ORDER BY price ASC;

Result:

| name                     | price  |
|--------------------------|--------|
| Cable Organiser          |   8.00 |
| SQL for Testers (book)   |  18.50 |
| Wireless Mouse           |  25.00 |
| Clean Code (book)        |  30.00 |
| Desk Lamp                |  42.00 |
| USB-C Hub                |  45.00 |
| Standing Desk Mat        |  55.00 |
| Webcam HD                |  75.00 |
| Mechanical Keyboard Pro  | 120.00 |
| 27-inch Monitor          | 320.00 |

Sorting by multiple columns

If two rows tie on the first column, ORDER BY uses the second column to break the tie:

-- Group by role, then alphabetical within each role
SELECT name, role FROM users
ORDER BY role ASC, name ASC;

Result:

| name        | role   |
|-------------|--------|
| Alice Khan  | admin  |
| Bob Patel   | tester |
| Carol Singh | tester |
| Esha Roy    | tester |
| Dan Müller  | viewer |

Each column has its own ASC/DESC. ORDER BY role ASC, created_at DESC sorts by role alphabetically, then within each role puts the newest first. Powerful for reports.

LIMIT — return only the first N rows

LIMIT cuts the result down to a fixed size after sorting. It's how you express "the top 5" or "the most recent one":

-- The 5 most expensive products
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 5;

Result:

| name                     | price  |
|--------------------------|--------|
| 27-inch Monitor          | 320.00 |
| Mechanical Keyboard Pro  | 120.00 |
| Webcam HD                |  75.00 |
| Standing Desk Mat        |  55.00 |
| USB-C Hub                |  45.00 |

-- The single most recent order
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 1;

LIMIT 1 after a DESC sort is the canonical "find the most recent X" pattern. You'll write it dozens of times in your testing career — to find the latest order for a user, the newest signup, the last bug filed by a tester.

OFFSET — skip rows for pagination

OFFSET N skips the first N rows before applying LIMIT. Combined with LIMIT, it implements pagination:

-- Page 1 (rows 1–10)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 0;
 
-- Page 2 (rows 11–20)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;
 
-- Page 3 (rows 21–30)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

A small dialect note:

MySQL, PostgreSQL, SQLite: LIMIT 10 OFFSET 20 (the form above).
SQL Server / older Oracle: OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY.

You'll mostly meet the LIMIT/OFFSET form. If your database refuses it, look up the alternative.

QA use cases for ORDER BY and LIMIT

Three patterns you'll use over and over:

-- 1. Find the most recent order for a specific user
-- (After a UI test creates an order, did it land?)
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 1;
 
-- 2. Top 3 largest orders — useful for revenue sanity checks
SELECT id, user_id, total FROM orders
ORDER BY total DESC
LIMIT 3;
 
-- 3. Verify that pagination matches what the API returns
-- API: GET /products?page=2&limit=5
-- DB equivalent: ORDER BY id LIMIT 5 OFFSET 5
SELECT id, name FROM products
ORDER BY id
LIMIT 5
OFFSET 5;

That third pattern is gold for API testing. The API claims a particular page; the database can prove it. If the rows don't match, either the API is wrong or you're misunderstanding the sort order — both are useful things to discover.

Always pair ORDER BY with the right column

Two rules of thumb:

For "most recent," sort by a timestamp. ORDER BY created_at DESC. Sorting by id DESC is usually the same — but only if ids are auto-incremented in time order, which can break under unusual loads or migrations.
For "largest" or "highest," sort by the numeric column you mean. ORDER BY total DESC for biggest orders, ORDER BY price DESC for most expensive products. Don't sort by a string column hoping for numeric order — '1000' < '99' lexicographically.

⚠️ Common Mistakes

LIMIT without ORDER BY. The result is "the first 5 rows in some unspecified order." That can change between runs and between databases — flaky tests follow. Always include ORDER BY when LIMIT is in play.
ORDER BY on a string column when you wanted numeric order. A version VARCHAR column sorts '10', '11', '2', '3' — alphabetically. If you need numeric order, store numbers as numbers, or CAST(...).
Pagination drift. If new rows get inserted between page 1 and page 2 of a paginated test, OFFSET-based pagination shifts and you can see the same row twice. For high-stakes pagination tests, capture page 1, then page 2, and verify there are no duplicates.

🎯 Practice Task

20 minutes. Run each query against the e-commerce database.

List all products sorted by price, cheapest first.
List all orders sorted by total, largest first.
Find the single most recent order — only one row in the result.
Find the 3 most expensive in-stock products. (Combine WHERE + ORDER BY + LIMIT.)
List all users sorted by role (alphabetical), and within each role by name (alphabetical).
Pretend the API returns products 10 at a time. Write the SQL equivalent of "page 2."
Stretch: Find the cheapest out-of-stock product. One row, lowest price, where in_stock = FALSE. This is the kind of query a PM might ask for ahead of a restock decision.

Next lesson we move from "show me individual rows" to "give me a single number that summarises a whole table" — DISTINCT, COUNT, SUM, AVG, MAX, MIN.