ORDER BY and LIMIT

Putting the results in order

So far, the archive has returned rows in no particular order. It has simply used whatever order was most convenient while fetching, joining, and filtering the data.

But you usually need more than a pile of rows—you need them in a useful order.

You can see this right away in Kotomarket:

• the most expensive products;
• the cheapest products;
• the newest orders;
• the products with the largest stock;
• users in alphabetical order;
• the latest events in the log.

That is what ORDER BY is for.

SELECT name, price
FROM products
ORDER BY price;

This query sorts the products by price.

By default, rows are sorted in ascending order:

ORDER BY price

is exactly the same as:

ORDER BY price ASC

ASC stands for ascending—from lowest to highest.

If you need the opposite direction, use DESC:

SELECT name, price
FROM products
ORDER BY price DESC;

DESC stands for descending—from highest to lowest.

For numbers, this is simple:

ASC  → 1, 2, 3, 4, 5
DESC → 5, 4, 3, 2, 1

Text is sorted alphabetically according to the rules of the database and its locale.

SELECT name, category
FROM products
ORDER BY name ASC;

This query sorts the products by name in ascending alphabetical order.

QUERY: The archive may return rows however it pleases. A report starts to make sense only when you choose the order yourself.

Without ORDER BY, row order is not guaranteed

Here is one rule worth remembering:

SQL does not guarantee the order of rows without ORDER BY.

For example:

SELECT id, name, price
FROM products;

This query may return the rows in one order today and a different order tomorrow.

Sometimes it looks as though the database returns rows:

• in the order they were inserted;
• by id;
• in their physical order in the table;
• in the order shown in the interface.

But you cannot rely on that.

The database may choose a different execution plan, use a different index, or read the data differently after its statistics are refreshed or the table changes. The result order can change even though the SQL query itself stays the same.

If the order matters, you must specify it explicitly:

SELECT id, name, price
FROM products
ORDER BY id;

or:

SELECT id, name, price
FROM products
ORDER BY price DESC;

The rule is simple:

No ORDER BY — no guaranteed order.

This is especially important before LIMIT. That is because LIMIT takes “the first rows,” and without a sort, there is no telling which rows the database will treat as first.

ASC and DESC

ORDER BY supports two sort directions.

ASC — ascending:

SELECT name, price
FROM products
ORDER BY price ASC;

For prices, that means:

the cheapest products first, then the most expensive ones.

DESC — descending:

SELECT name, price
FROM products
ORDER BY price DESC;

For prices, that means:

the most expensive products first, then the cheapest ones.

If you don’t specify a direction, ASC is used.

So these two queries are equivalent:

SELECT name, price
FROM products
ORDER BY price;

SELECT name, price
FROM products
ORDER BY price ASC;

When you are starting out, it helps to read the query aloud:

ORDER BY price ASC

— sort by price from lowest to highest.

ORDER BY price DESC

— sort by price from highest to lowest.

LIMIT: keeping only the first rows

LIMIT controls the maximum number of rows returned.

For example:

SELECT name, price
FROM products
LIMIT 3;

This query returns only three rows.

But on its own, LIMIT says nothing about which rows you want. It simply keeps the first rows from whatever result the database produces.

That is why LIMIT is almost always paired with ORDER BY when you want a meaningful result.

The three cheapest products:

SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 3;

The three most expensive products:

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

The newest orders:

SELECT id, user_id, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 10;

The logic is always the same:

1. ORDER BY puts the rows in the order you want.
2. LIMIT keeps the requested number of rows from the beginning.

ORDER BY price DESC
LIMIT 5

means:

put the most expensive products first and take the first five.

Why ORDER BY comes near the end of a query

In SQL, the order in which you write the clauses does not quite match the order in which SQL logically processes them.

For example:

SELECT name, price
FROM products
WHERE category = 'Книги'
ORDER BY price DESC
LIMIT 5;

You can think through this query step by step:

1. FROM products — start with the products table.
2. WHERE category = 'Книги' — keep only the books.
3. SELECT name, price — choose the columns to return.
4. ORDER BY price DESC — sort the remaining rows by price.
5. LIMIT 5 — keep the first five rows.

So the sort applies to the rows that remain after filtering, not to the entire table.

If the table contains a thousand products but only twenty books, the query first keeps the books, then sorts them by price, and only then returns the five most expensive ones.

Here is a useful way to think about these clauses:

WHERE

determines which rows are included.

ORDER BY

determines the order in which they appear.

LIMIT

determines how many of the first rows to keep.

Sorting by several columns

Sometimes one sort key is not enough.

Suppose you need to sort the products:

1. first by category;
2. within each category — by price, from highest to lowest.

To do that, list several expressions in ORDER BY, separated by commas:

SELECT name, category, price
FROM products
ORDER BY category ASC, price DESC;

You can read this query as follows:

first sort by category in ascending order, then sort products within each category by price in descending order.

Here is an example result:

The first sort key is the primary one:

category ASC

It sorts the rows by category.

The second key breaks ties in the first:

price DESC

It orders the products within each category.

You can choose a separate direction for each column:

ORDER BY category ASC, price DESC, name ASC

That means:

1. category in ascending order;
2. within a category, price in descending order;
3. if the price is the same, name in ascending order.

Sorting by an expression

You can sort by a calculation as well as by an existing column.

For example, the products table has:

• price — the price of the product;
• stock — the quantity left in stock.

If you want to see which products tie up the most money in the warehouse, you can calculate their inventory value:

price * stock

Consider this query:

SELECT name, price, stock
FROM products
ORDER BY price * stock DESC;

It sorts the products by total inventory value, rather than by price or stock alone.

So an item that costs 1000 with 100 units in stock can rank higher than one that costs 5000 with 2 units in stock.

Here is why:

1000 * 100 = 100000
5000 * 2   = 10000

ORDER BY can also sort by expressions like this:

ORDER BY price * stock DESC

This is useful when the order depends on a calculation rather than a single column.

Sorting by an alias

If an expression is long, repeating it in ORDER BY can be tedious.

Instead, you can give the expression a name by assigning it an alias:

SELECT
    name,
    price,
    stock,
    price * stock AS stock_value
FROM products
ORDER BY stock_value DESC;

Here:

price * stock AS stock_value

creates a calculated column named stock_value in the result.

Then:

ORDER BY stock_value DESC

sorts by that calculated value.

This works because ORDER BY is logically applied after the SELECT list has been built. By the time sorting begins, the alias stock_value already exists.

But WHERE works differently.

This is not allowed:

SELECT
    name,
    price,
    stock,
    price * stock AS stock_value
FROM products
WHERE stock_value > 10000;

In WHERE, the alias from SELECT does not exist yet.

The correct approach is to repeat the expression:

SELECT
    name,
    price,
    stock,
    price * stock AS stock_value
FROM products
WHERE price * stock > 10000
ORDER BY stock_value DESC;

or use a subquery—but that is a topic for later modules.

The key idea of this lesson:

In ORDER BY you can use an alias from SELECT. In WHERE you cannot.

Where NULLs appear when you sort

NULL represents a missing value. When sorting, you need to decide where rows with missing values should appear:

• at the beginning;
• at the end.

By default, PostgreSQL treats a NULL as greater than any non-null value when sorting.

So with an ascending sort:

ORDER BY city ASC

rows with NULL appear at the end.

And with a descending sort:

ORDER BY city DESC

rows with NULL appear at the beginning.

To avoid relying on defaults, you can state the position explicitly:

ORDER BY city ASC NULLS FIRST

That means:

sort the cities in ascending order, but put the rows without a city at the beginning.

Or:

ORDER BY city ASC NULLS LAST

That means:

sort the cities in ascending order, and put the rows without a city at the end.

You can use it with DESC too:

ORDER BY price DESC NULLS LAST

This is useful for top lists.

For example, if some products have an unknown price, this query:

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;

may put products with NULL at the top in PostgreSQL, because with DESC, the NULLs come first.

To get the most expensive products with known prices, it is better to write:

SELECT name, price
FROM products
ORDER BY price DESC NULLS LAST
LIMIT 5;

That way, products with unknown prices do not crowd products with known prices out of the top five.

A note on portability: the default position of NULLs can vary between database systems. In PostgreSQL, you can specify NULLS FIRST or NULLS LAST explicitly. MySQL does not support this syntax, and its default behavior is the opposite: a NULL is usually treated as smaller than a non-null value.

Ties: when values are the same

Imagine that we are choosing the three most expensive products:

SELECT id, name, price
FROM products
ORDER BY price DESC
LIMIT 3;

If every product has a different price, the order is clear.

But what if several products have the same price?

For example:

The first three products have the same price.

The clause:

ORDER BY price DESC

guarantees only one thing:

products priced at 3000 will appear before products priced at 1000.

It does not guarantee the relative order of the three products priced at 3000.

That may seem like a minor detail, but it matters for reports, tests, and paginated results.

If several rows have the same value in the sort column, their relative order is undefined. It can change after a data update, if the execution plan changes, or even between two nearly identical runs.

To make the order completely predictable, add a second sort key—usually the unique id:

SELECT id, name, price
FROM products
ORDER BY price DESC, id ASC
LIMIT 3;

Now you can read the sort like this:

1. first by price, from highest to lowest;
2. if the price is the same — by id, from lowest to highest.

Now the order is stable.

A stable order for LIMIT and pagination

A non-deterministic order is especially risky when combined with LIMIT.

For example:

SELECT id, name, price
FROM products
ORDER BY price DESC
LIMIT 10;

If many products share the same price, the database may choose any ten of the tied rows at the cutoff.

A product may make the top 10 today.
Tomorrow, even if the prices have not changed, it may not.

That is not a database bug. You simply did not specify a complete sort order.

For a stable result, add a unique column at the end of the sort:

SELECT id, name, price
FROM products
ORDER BY price DESC, id ASC
LIMIT 10;

This is especially important for pagination.

For example, the first page:

ORDER BY price DESC
LIMIT 10

and the second page using an offset:

ORDER BY price DESC
LIMIT 10 OFFSET 10

If several rows share a price and there is no extra sort by id, the same row can “jump” between pages.

A safer version is:

ORDER BY price DESC, id ASC
LIMIT 10 OFFSET 10

The main rule:

If you use LIMIT for a report, a top list or a page, the sort order must fully determine the order of the rows.

Can you sort by a column that is not in SELECT?

Sometimes you do not want technical columns to appear in a report, even though you still need to sort by them.

For example, suppose you want to show product names but sort them by price:

SELECT name
FROM products
ORDER BY price DESC;

That is allowed.

price does not have to be in the SELECT list for you to use it in ORDER BY.

The query returns only name, but price determines the order of the rows.

This is useful when a column controls the output order but the reader does not need to see it.

For example:

SELECT name
FROM products
ORDER BY created_at DESC
LIMIT 10;

This returns the names of the ten newest products without displaying their creation dates.

Typical ORDER BY and LIMIT combinations

The ORDER BY + LIMIT combination appears throughout SQL.

The most expensive products:

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;

The cheapest products:

SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 5;

The newest orders:

SELECT id, user_id, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 10;

The first 20 users alphabetically:

SELECT id, name
FROM users
ORDER BY name ASC
LIMIT 20;

The products with the highest stock levels:

SELECT name, stock
FROM products
ORDER BY stock DESC
LIMIT 10;

The products with the highest inventory value:

SELECT
    name,
    price,
    stock,
    price * stock AS stock_value
FROM products
ORDER BY stock_value DESC
LIMIT 10;

In every case, the pattern is the same:

ORDER BY what_matters_most
LIMIT how_many_rows_you_need

QUERY: First tell the archive what matters most. Then ask it for the top results.