SQL ORDER BY, LIMIT and DISTINCT: Sort and Trim Results

At the end of March, Malwa Mart's owner pins a "Top 3 orders of the month" sheet on the staff noticeboard at the Rajwada branch. To make it, a clerk sorts all the bills from largest to smallest, copies the first three, and stops.

The owner also wants a list of every payment method customers used. The clerk does not write "UPI" forty times. She writes each method once.

Sorting the bills is ORDER BY. Copying only the first three is LIMIT. Writing each payment method once is DISTINCT. These three tools turn a raw pile of rows into something a person can read in five seconds.

ORDER BY sorts the rows of a result, LIMIT restricts how many rows are returned, and DISTINCT removes duplicate rows, all applied after the WHERE filter has run.

In the previous article you learned to pick rows with SQL SELECT and WHERE. This one shapes those rows into ranked, trimmed, de-duplicated answers.

How does ORDER BY sort results?

ORDER BY goes near the end of the query and names the column to sort on. ASC sorts from smallest to largest and is the default. DESC reverses it.

SELECT order_id, order_date, amount
FROM orders
ORDER BY amount DESC;

Numbers sort by value, dates in ISO text sort by calendar order, and text sorts alphabetically according to the database's collation, its rules for comparing characters.

Sorting by several columns

When the first column has ties, the second column decides. This is how you get a stable, predictable order.

SELECT order_id, order_date, amount
FROM orders
ORDER BY order_date DESC, amount DESC;

Read it as "newest date first, and within the same date, biggest amount first". Each column gets its own direction; DESC does not carry over to the next column.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE orders (order_id INT, customer_id INT, store_id INT, order_date TEXT, amount INT, payment_mode TEXT)")
con.executemany("INSERT INTO orders VALUES (?, ?, ?, ?, ?, ?)", [
    (101, 1, 1, "2026-03-01", 1040, "UPI"), (102, 2, 2, "2026-03-01", 330, "Cash"),
    (103, 1, 1, "2026-03-03", 275, "UPI"), (104, 3, 3, "2026-03-04", 180, "Card"),
    (105, 4, 2, "2026-03-05", 680, "UPI"), (106, 2, 4, "2026-03-07", 120, "Cash"),
    (107, 5, 3, "2026-03-08", 1560, "Card"), (108, 6, 1, "2026-03-08", 220, "UPI"),
    (109, 4, 4, "2026-03-10", 340, None), (110, 1, 2, "2026-03-12", 465, "Cash")])

print("By amount, largest first:")
print([r for r in con.execute("SELECT order_id, amount FROM orders ORDER BY amount DESC")][:4])
print("By date (newest first), then amount (largest first):")
for row in con.execute("SELECT order_date, order_id, amount FROM orders ORDER BY order_date DESC, amount DESC"):
    print(row)

Orders 107 and 108 share 8 March, and 101 and 102 share 1 March. In both pairs the larger amount comes first because of the second sort column.

Sorting by an expression or alias

You can sort by a calculated value, and in every major database you can sort by a SELECT alias, because ORDER BY runs after SELECT.

SELECT product_name, price, price * 1.05 AS price_with_tax
FROM products
ORDER BY price_with_tax DESC;

You may also see ORDER BY 2, meaning "the second column in SELECT". It works, but it breaks silently when someone reorders the columns, so prefer names.

Where do NULLs go when you sort?

Databases disagree on this, and it matters in reports. Order 109 has no payment mode.

When the position matters, say it explicitly with NULLS LAST. In MySQL, use the trick ORDER BY payment_mode IS NULL, payment_mode, which sorts the 0s (not null) before the 1s (null).

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE orders (order_id INT, payment_mode TEXT)")
con.executemany("INSERT INTO orders VALUES (?, ?)",
                [(104, "Card"), (106, "Cash"), (108, "UPI"), (109, None)])

for clause in ["payment_mode", "payment_mode NULLS LAST", "payment_mode IS NULL, payment_mode"]:
    rows = con.execute(f"SELECT order_id, payment_mode FROM orders ORDER BY {clause}").fetchall()
    print(f"ORDER BY {clause:36} {rows}")

How do LIMIT and OFFSET work?

LIMIT n returns at most n rows. Combined with ORDER BY, it answers every "top N" question.

SELECT order_id, amount
FROM orders
ORDER BY amount DESC
LIMIT 3;

OFFSET k skips the first k rows before LIMIT counts. This is how websites show results page by page. For page number p with n rows per page, the offset is (p - 1) * n.

With a page size of 3, page 1 uses OFFSET 0, page 2 uses OFFSET 3 and page 3 uses OFFSET 6. Sorting first keeps every page consistent.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE orders (order_id INT, amount INT)")
con.executemany("INSERT INTO orders VALUES (?, ?)", [
    (101, 1040), (102, 330), (103, 275), (104, 180), (105, 680),
    (106, 120), (107, 1560), (108, 220), (109, 340), (110, 465)])

top3 = con.execute("SELECT order_id, amount FROM orders ORDER BY amount DESC LIMIT 3").fetchall()
print("Top 3 orders:", top3)

page_size = 3
for page in range(1, 5):
    offset = (page - 1) * page_size
    rows = con.execute("SELECT order_id FROM orders ORDER BY amount DESC LIMIT ? OFFSET ?",
                       (page_size, offset)).fetchall()
    print(f"Page {page} (OFFSET {offset}):", [r[0] for r in rows])

The fourth page holds just one order, because ten rows do not divide evenly into threes. Passing the numbers as placeholders keeps the query safe when the page number comes from a web request.

LIMIT in other databases

LIMIT is not part of the SQL standard, so syntax varies.

How does DISTINCT remove duplicates?

DISTINCT comes right after SELECT and removes rows that are exact repeats of another row in the result.

SELECT DISTINCT payment_mode FROM orders;

With several columns, DISTINCT looks at the combination. SELECT DISTINCT store_id, payment_mode keeps one row for each store-and-method pair, so a store that took both UPI and Cash appears twice. NULLs count as equal to each other for DISTINCT, so all missing values collapse into one row.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE orders (order_id INT, store_id INT, payment_mode TEXT)")
con.executemany("INSERT INTO orders VALUES (?, ?, ?)", [
    (101, 1, "UPI"), (102, 2, "Cash"), (103, 1, "UPI"), (104, 3, "Card"), (105, 2, "UPI"),
    (106, 4, "Cash"), (107, 3, "Card"), (108, 1, "UPI"), (109, 4, None), (110, 2, "Cash")])

print("All rows:", con.execute("SELECT COUNT(*) FROM orders").fetchone()[0])
print("Payment modes:", con.execute("SELECT DISTINCT payment_mode FROM orders ORDER BY payment_mode").fetchall())
pairs = con.execute("SELECT DISTINCT store_id, payment_mode FROM orders ORDER BY store_id, payment_mode").fetchall()
print(len(pairs), "store-mode pairs:", pairs)

Ten orders collapse into four payment modes, including None, and six store-mode pairs. To count unique values instead of listing them, use COUNT(DISTINCT payment_mode), covered in SQL aggregate functions.

The order these clauses run in

You write SELECT DISTINCT ... ORDER BY ... LIMIT, but the database applies them in a fixed logical sequence. Knowing it explains why LIMIT without ORDER BY gives random-looking results.

Rows are filtered first, then columns are chosen and de-duplicated, then sorted, and only then trimmed.

1. FROM picks the table.
2. WHERE filters rows.
3. SELECT computes the output columns and aliases.
4. DISTINCT removes duplicate result rows.
5. ORDER BY sorts what is left.
6. LIMIT / OFFSET keeps the requested slice.

GROUP BY and HAVING slot in between WHERE and SELECT; you will meet them after aggregates. One consequence: in PostgreSQL, when you use DISTINCT, every ORDER BY expression must also appear in the SELECT list.

Common mistakes with ORDER BY, LIMIT and DISTINCT

• LIMIT without ORDER BY. The database may return any rows, and the answer can change between runs.
• Forgetting the tie-breaker. If two orders have the same amount, page 1 and page 2 can swap them. Add a unique column like order_id as the last sort key.
• Assuming DESC applies to every column. ORDER BY store_id, amount DESC sorts stores ascending.
• Using DISTINCT to hide a bad join. If a join creates duplicates, fix the join; DISTINCT only masks it.
• Sorting text with mixed case. SQLite's default collation puts Zebra before apple. Use ORDER BY LOWER(name) or SQLite's COLLATE NOCASE.

Want feedback on queries like these from a trainer? The analyst training programme at Debugshala runs offline classes in Indore with a free demo session.

Key takeaways

• ORDER BY col [ASC|DESC] sorts; extra columns break ties, each with its own direction.
• NULL placement differs by database; use NULLS FIRST or NULLS LAST where supported.
• LIMIT n OFFSET k gives top-N lists and pages; always pair it with ORDER BY.
• DISTINCT removes duplicate rows across all selected columns.
• Next, see these clauses combined in SQL for data analysis, then learn aggregate functions.