ORDER BY & LIMIT

ORDER BY controls row order, and LIMIT controls how many sorted rows you keep. Without ORDER BY, SQL does not promise which row comes first, even if a tiny table seems to come back the same way every time.

You already choose columns with SELECT and rows with WHERE. Now you choose the order of the answer.

Sorting rows with ORDER BY

A sort key is the column or expression PostgreSQL uses to line up rows. ASC means ascending: small-to-large for numbers, A-to-Z for text. DESC means descending: large-to-small or Z-to-A.

This query lines up the menu by category, then puts the most expensive item first inside each category, then uses name as the final tie-breaker, a later sort key used when earlier sort keys are equal.

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

      name       | category | price
-----------------+----------+-------
 Cold Brew       | coffee   |  4.50
 Flat White      | coffee   |  4.00
 Cappuccino      | coffee   |  3.80
 Espresso        | coffee   |  2.50
 Sparkling Water | drinks   |  2.20
 Cinnamon Roll   | pastry   |  4.10
 Banana Bread    | pastry   |  3.60
 Croissant       | pastry   |  3.20
 Sencha          | tea      |  3.40
 Earl Grey       | tea      |  3.00
(10 rows)

ASC is the default, so ORDER BY category means the same as ORDER BY category ASC. Still, writing ASC while you learn makes the direction visible. The last sort key, name ASC, is a professional habit: if two pastries ever share a price, the output still has a stable order.

Sorting by expressions, aliases, and ordinals

ORDER BY can sort by a plain column, by an expression, which is a calculation that returns a value, or by a name you gave that expression. Here the cafe sorts a 10%-off preview by the computed sale price:

SELECT name,
       price,
       price * 0.9 AS sale_price
FROM menu_items
ORDER BY sale_price DESC, name ASC;

      name       | price | sale_price
-----------------+-------+------------
 Cold Brew       |  4.50 |      4.050
 Cinnamon Roll   |  4.10 |      3.690
 Flat White      |  4.00 |      3.600
 Cappuccino      |  3.80 |      3.420
 Banana Bread    |  3.60 |      3.240
 Sencha          |  3.40 |      3.060
 Croissant       |  3.20 |      2.880
 Earl Grey       |  3.00 |      2.700
 Espresso        |  2.50 |      2.250
 Sparkling Water |  2.20 |      1.980
(10 rows)

That AS sale_price is an alias preview: 2.4 makes aliases official. For now, notice the practical rule: ORDER BY can use sale_price after the select list creates it.

You will also see ordinals, position numbers from the select list: ORDER BY 3 DESC, 1 ASC sorts by the third selected column, then the first. It works, but it is brittle. Move a column in the select list and the sort silently changes. Prefer names unless you are reading someone else's query.

Sorting missing values

Sometimes the value you sort on is missing. SQL calls that missing or unknown marker NULL; Section 3 explains its logic. For sorting, you only need the placement controls: NULLS FIRST puts missing values at the top, and NULLS LAST puts them at the bottom.

The staff bonuses have two undecided values. For a leaderboard, put those unknown bonuses last:

SELECT name, bonus
FROM staff
ORDER BY bonus DESC NULLS LAST, name ASC;

 name  | bonus
-------+--------
 Ken   | 400.00
 Yuki  | 250.00
 Ada   | 120.00
 Priya |   0.00
 Rosa  |   NULL
 Tomas |   NULL
(6 rows)

For an audit list, you might want the missing bonuses first:

SELECT name, bonus
FROM staff
ORDER BY bonus ASC NULLS FIRST, name ASC;

 name  | bonus
-------+--------
 Rosa  |   NULL
 Tomas |   NULL
 Priya |   0.00
 Ada   | 120.00
 Yuki  | 250.00
 Ken   | 400.00
(6 rows)

The key is to say what humans expect. Do not leave missing-value placement as a surprise.

LIMIT, OFFSET, and the top rows

A top-N query asks for the first N rows after sorting. The cafe wants the three priciest menu items:

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

     name      | price
---------------+-------
 Cold Brew     |  4.50
 Cinnamon Roll |  4.10
 Flat White    |  4.00
(3 rows)

LIMIT 3 does not mean "three important rows." It means "three rows after the ordering step." That is why the ORDER BY comes first in the query.

OFFSET skips rows after sorting. Page two of the same price list skips the first three, then takes the next three:

SELECT name, price
FROM menu_items
ORDER BY price DESC, name ASC
LIMIT 3 OFFSET 3;

     name     | price
--------------+-------
 Cappuccino   |  3.80
 Banana Bread |  3.60
 Sencha       |  3.40
(3 rows)

OFFSET is fine for learning and shallow pages. Later, in the performance section, you will meet better patterns for deep paging. The beginner habit is simpler: always decide the order before you decide the page.

You can now make a result grid stable: filter the rows, sort them, then take the amount you need. Next, 2.4 cleans up result shapes with aliases, DISTINCT, and formatting habits that make longer SELECT statements readable.