Why a table has no order until you ask

The idea

A table is a pile of rows, not a list. There is no first row and no last row. The order you see when you run SELECT * FROM products is whatever order the database found convenient, and it can change after an update, a different plan or a different machine.

Order only exists when you ask for it with ORDER BY. And LIMIT has no sense of order on its own: it takes "the first N rows" of whatever order exists at that moment. So every "top 10", "latest 5" or "page 2" is really two steps: sort, then cut. If the sort is not fully defined, the cut is not either.

Why it exists

"Top 10 products", "the 5 most recent orders" and "page 2 of the price list" make up half the requests analysts get. They must be repeatable. If the homepage shows a different bestseller list on every refresh because of ties, someone notices. Databases keep rows in whatever layout is fastest to write, which is why the order is left undefined unless you say otherwise.

LIMIT exists because a result of 12,000 rows is almost never what you need. Sending three rows instead of twelve thousand is faster, easier to check and easier to show.

There is a second reason to care. A sorted result is a promise you make to the reader. When a dashboard says "top 10", people act on it: they restock, discount or write about those products. A list that quietly depends on luck is a list you cannot defend when someone asks why product 184 is missing today.

How it works under the hood

Sorting and cutting happen at the end of the query, after rows are filtered, grouped and built:

1. The database finishes FROM, WHERE, GROUP BY and SELECT, giving a set of output rows.
2. ORDER BY arranges them by the first column. When two rows have equal values, it looks at the second column, then the third, and so on.
3. OFFSET skips that many rows from the top.
4. LIMIT keeps the next N rows and throws away the rest.

Because ORDER BY runs after SELECT, it can use your aliases and even the position of an output column (ORDER BY 2). Position is fragile: add a column to SELECT and the sort changes. Prefer names.

Ties and the tie-breaker

Whenever the sort column can repeat, rows with equal values are in an undefined order among themselves. That does no harm in a long list. It matters the moment LIMIT cuts through the tie. The fix is one more column that is unique, usually the id: ORDER BY unit_price, product_id. It costs nothing and turns an arbitrary answer into a stable one. Treat it as part of every top-N query. The levels repeat the habit until it is automatic.

Checking a top-N list

Two quick tests catch most mistakes. First, run the query without LIMIT and look at where the cut falls: if the rows just above and below it share the sort value, you have a tie and need a tie-breaker. Second, flip DESC to ASC and see whether the opposite end of the list makes sense. A "top 10 refunds" query that starts with a negative number or a NULL has a data problem, not a SQL one.

Paging

A page is a window onto a sorted list. Page p of size n is LIMIT n OFFSET (p - 1) * n.

Paging only works with a deterministic order. If ties can reshuffle between requests, a row can appear on page 2 and again on page 3, or never. There is also a cost: to reach OFFSET 10000 the database still sorts and walks past 10,000 rows. That is fine for a staff directory and a poor fit for scrolling through millions of events.

What "smaller" means for text and dates

Numbers sort by value, dates by time, text by alphabet. A text column of numbers sorts as text, so '100' comes before '20' because the first character is compared first. Timestamps sort correctly because the format puts the biggest unit first, which is why ORDER BY order_date DESC LIMIT 5 really does give the five most recent orders. If a sort looks scrambled, check the column's type before anything else.

Direction and NULLs

ASC is the default and DESC reverses it. Each column in the list has its own direction: ORDER BY category, unit_price DESC groups by category and shows the dearest product first inside each. NULLs need a decision, because they are neither small nor large. Different databases put them at the start or at the end for the same query, so when it matters, say so with NULLS FIRST or NULLS LAST instead of trusting a default.

Common misconceptions

"The table is already in order, I don't need ORDER BY"

It looks ordered because the rows were loaded in id order and nothing has changed yet. Add an index, update a row or move the data to another server and it can change. If your result depends on order, write ORDER BY, every time.

"ORDER BY plus LIMIT always gives the same rows"

Only if the sort is fully determined. With ties at the cut, two runs can return different rows, and both are correct as far as SQL is concerned. Add a unique column last in the ORDER BY and the answer becomes the same each time.

"DESC means the biggest values come first, NULLs included"

NULLs do not follow your intuition. In some databases they sort as the largest value and come first in a DESC list, in others they come last. A "most expensive first" list can start with products whose price is missing. Filter them out or place them explicitly with NULLS LAST.