ORDER BY and LIMIT for Reporting

"Show me the top 10 customers." "What were our five biggest orders?" "List the most recent sales first." Ranked and trimmed reports are everywhere in analytics, and they are built from two clauses: ORDER BY to sort and LIMIT to keep only the rows you want. On their own they are simple; the discipline is remembering to sort before you trim, so that "top" actually means top.

This tutorial follows SELECT and WHERE in the SQL for analytics path. Once you can scope data to the right rows, ranking it is what turns a raw list into a report someone reads top to bottom.

Sorting with ORDER BY

ORDER BY sorts the output by one or more columns. The default is ascending (ASC); add DESC for descending. Here is the sample orders table:

-- orders
order_id | customer | region | amount | order_date
---------+----------+--------+--------+-----------
1001     | Aarti    | South  | 1200   | 2026-01-05
1002     | Bhaskar  | South  | 8500   | 2026-01-11
1003     | Chitra   | West   | 1200   | 2026-02-02
1004     | Aarti    | South  | 600    | 2026-02-18
1005     | Devan    | North  | 8500   | 2026-03-09
1006     | Chitra   | West   | 2100   | 2026-03-22

To list orders from highest amount to lowest:

SELECT customer, amount
FROM orders
ORDER BY amount DESC;

customer | amount
---------+-------
Bhaskar  | 8500
Devan    | 8500
Chitra   | 2100
Aarti    | 1200
Chitra   | 1200
Aarti    | 600

Sorting by multiple columns

When the first sort column has ties, a second column breaks them. To sort by region alphabetically, then by amount high to low within each region:

SELECT region, customer, amount
FROM orders
ORDER BY region ASC, amount DESC;

region | customer | amount
-------+----------+-------
North  | Devan    | 8500
South  | Bhaskar  | 8500
South  | Aarti    | 1200
South  | Aarti    | 600
West   | Chitra   | 2100
West   | Chitra   | 1200

The database sorts by region first and only uses amount to order rows that share a region. This multi-key sorting is how you produce reports grouped visually by one dimension and ranked within it by another.

Trimming with LIMIT

LIMIT n keeps only the first n rows after sorting. The classic "top 3 orders by value":

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

customer | amount
---------+-------
Bhaskar  | 8500
Devan    | 8500
Chitra   | 2100

The order matters conceptually and in execution: the database sorts the whole result, then hands you the top three. Remove the ORDER BY and LIMIT 3 still returns three rows, but they are whatever the database happened to read first — meaningless as a "top" list.

Paging with OFFSET

To get the second page of results — rows 4 through 6 — skip the first three with OFFSET:

SELECT customer, amount
FROM orders
ORDER BY amount DESC
LIMIT 3 OFFSET 3;

This underlies pagination in dashboards and exports. A stable ORDER BY is essential here; if the sort is ambiguous, pages can overlap or drop rows between requests.

Practical usage: the everyday ranked report

Most "leaderboards" combine sorting and trimming with a summary. "Top 3 customers by total spend" needs grouping first, then ranking:

SELECT customer,
       SUM(amount) AS total_spend
FROM orders
GROUP BY customer
ORDER BY total_spend DESC
LIMIT 3;

customer | total_spend
---------+------------
Bhaskar  | 8500
Devan    | 8500
Chitra   | 3300

This is one of the most-written query shapes in analytics: aggregate to a per-entity total, sort descending, keep the top N. Note that ORDER BY can sort by an alias like total_spend even though it is a computed column, because sorting happens after the SELECT list is built.

A subtle but important detail: sorting a text column is not always the sort a business wants. Product codes like A2, A10, A100 sort as A10, A100, A2 because text comparison is character by character, not numeric. When a column stores numbers as text, cast it — ORDER BY CAST(code_number AS INTEGER) — or the "ranking" will be alphabetical nonsense. Analysts hit this constantly with month names, version strings and ID codes, so check the underlying data type before trusting a sort. Case sensitivity varies too: some databases sort uppercase before lowercase, which can scatter names that should sit together.

Common mistakes

• LIMIT without ORDER BY for a "top N". The result looks plausible but is not actually ranked and can change run to run. Always sort first.
• Assuming a default sort. Tables have no inherent order. If you do not specify ORDER BY, the sequence is undefined even if it looks sorted by ID on your machine.
• Ties at the cutoff. LIMIT 3 on data where the 3rd and 4th rows tie will arbitrarily include one and drop the other. When ties matter, consider window functions like RANK instead of a hard LIMIT.
• Wrong dialect keyword. Writing LIMIT on SQL Server or Oracle fails; they use TOP or FETCH FIRST. Know your database.

In interviews

"Return the top 5 customers by revenue" is an interview staple, and the follow-up is almost always about ties: "what if two customers tie for 5th place — does LIMIT handle that correctly?" The expected answer is that LIMIT picks arbitrarily among ties, and a fair ranking needs RANK() or DENSE_RANK() from window functions. Showing you know when LIMIT is insufficient signals real analyst maturity.

Where this fits in your learning path

Sorting and trimming complete the basic reporting trio alongside SELECT and WHERE and aggregate functions. For fair "top N per group" rankings that LIMIT cannot express, continue to window functions. These ranking skills recur throughout the data analyst roadmap.