Skip to content
aviral gupta
Get in touch

// B2.2 · ~30 min · Beginner

ORDER BY, LIMIT and OFFSET

After this lesson you can put a result in the order a question needs, decide where missing values go, and return only the first rows or one page of them.

Lesson 2 of 5 in B2 Querying one table

You will be able to

  • Sort with ORDER BY: ascending or descending, by several keys, by alias or position
  • Say where NULLs sort by default, and move them with NULLS FIRST or NULLS LAST
  • Take the first rows or one page with LIMIT, OFFSET or FETCH FIRST, on an order that is unique

  1. Warm-up · Activity 1 of 7

    Warm-up: SELECT title FROM books; has no ORDER BY. In which order does PostgreSQL promise to return the rows?

  2. Predict · Activity 2 of 7

    Predict before you read on. The members joined on 2025-01-15 (Ada), 2025-03-02 (Grace), 2026-02-10 (Linus) and 2026-05-20 (Margaret). What does this return?

    SELECT name FROM members
ORDER BY joined DESC
LIMIT 2;

  3. Practice · Activity 3 of 7

    Fill in the key word so that the newest book comes first.

    SELECT title, year FROM books ORDER BY year ____;
    SELECT title, year FROM books ORDER BY year ;

  4. Practice · Activity 4 of 7

    Match each clause to what it does.

  5. Practice · Activity 5 of 7

    Put the rows in the order this query returns them: SELECT title, copies FROM books ORDER BY copies DESC, title LIMIT 4;

    1. 1.Dune (2 copies)
    2. 2.Beloved (1 copy)
    3. 3.Emma (1 copy)
    4. 4.The Hobbit (3 copies)

  6. Brain teaser · Activity 6 of 7

    Brain teaser. Ada and Linus live in Berlin, Grace in Munich, and Margaret has no city (NULL). Who comes first?

    SELECT name FROM members
ORDER BY city DESC
LIMIT 1;

  7. Apply · Activity 7 of 7

    Mini-task. With the library of the worked example, write two queries: page 2 of the catalogue, two books per page, sorted by title (title and year); and the member list sorted by city, with members who have no city first and the same city sorted by name. Then change the page number and predict the rows before you run it.

    Check your work against this list

Build it yourself

Read the worked example, then write the exercises. Your code runs in your browser or on your computer and is never uploaded.

Worked example

Four orders of the library

setup.sql loads the books and members of the library from module B1. main.sql sorts them four ways: newest books first with a limit, by three keys, with the members without a city first, and one page of two books. On your computer: psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- 1. The three newest books.
SELECT title, year
FROM books
ORDER BY year DESC
LIMIT 3;

-- 2. By genre, then most copies first, then title.
SELECT genre, title, copies
FROM books
ORDER BY genre, copies DESC, title;

-- 3. Members without a city first, then by name.
SELECT name, city
FROM members
ORDER BY city NULLS FIRST, name;

-- 4. Page 2 of the catalogue, two books per page.
SELECT id, title
FROM books
ORDER BY id
LIMIT 2 OFFSET 2;

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

Run it with

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Output

    title    | year
-------------+------
 Beloved     | 1987
 Neuromancer | 1984
 Kindred     | 1979
(3 rows)

  genre  |    title    | copies
---------+-------------+--------
 classic | Emma        |      1
 fantasy | The Hobbit  |      3
 fiction | Beloved     |      1
 sci-fi  | Dune        |      2
 sci-fi  | Kindred     |      1
 sci-fi  | Neuromancer |      1
(6 rows)

   name   |  city
----------+--------
 Margaret |
 Ada      | Berlin
 Linus    | Berlin
 Grace    | Munich
(4 rows)

 id |  title
----+---------
  3 | Kindred
  4 | Beloved
(2 rows)
  • ORDER BY year DESC sorts first, and LIMIT 3 then keeps the first three rows of the sorted result.
  • The three sci-fi books are equal in genre, so copies DESC decides (Dune first); Kindred and Neuromancer tie on copies too, so title decides.
  • Margaret has no city. NULLS FIRST moves her to the top; without it she would come last.
  • OFFSET 2 skips ids 1 and 2, and LIMIT 2 returns the next two: page 2.
Change it and run it

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Exercises

Exercise 1 of 2

The oldest books and the most copies

Write two queries. First: the title and year of the two oldest books, oldest first, with LIMIT. Second: the title and copies of the three books with the most copies, most first, and books with the same number of copies in alphabetical order; use FETCH FIRST instead of LIMIT.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    ORDER BY year sorts the oldest first; LIMIT 2 then keeps two rows.

  2. Hint 2

    Four books have one copy. A second sort key, title, decides which of them comes first.

  3. Hint 3

    ORDER BY copies DESC, title FETCH FIRST 3 ROWS ONLY;

Show a solution

One way to solve it. Yours can look different and still pass the checks.

SELECT title, year
FROM books
ORDER BY year
LIMIT 2;

SELECT title, copies
FROM books
ORDER BY copies DESC, title
FETCH FIRST 3 ROWS ONLY;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- setup.sql has loaded books and members.
SELECT title, year, copies FROM books;

test.sql

-- test: The two queries print the two oldest books, then the three with the most copies
-- output:
--    title    | year
-- ------------+------
--  Emma       | 1815
--  The Hobbit | 1937
-- (2 rows)
--
--    title    | copies
-- ------------+--------
--  The Hobbit |      3
--  Dune       |      2
--  Beloved    |      1
-- (3 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Exercise 2 of 2

Page three of the catalogue

The catalogue lists the books alphabetically by title, two per page. The starter should show page 3 (title and author), but it has no ORDER BY, so it shows some two rows. Fix it so it returns the fifth and sixth titles in alphabetical order. Then add a second query: the name and join date of the member who joined second most recently.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    Add ORDER BY title before LIMIT: without it, “page 3” has no meaning.

  2. Hint 2

    Page n with two rows per page skips 2 × (n − 1) rows: OFFSET 4 for page 3.

  3. Hint 3

    The second newest: ORDER BY joined DESC, skip one row, take one: LIMIT 1 OFFSET 1.

Show a solution

One way to solve it. Yours can look different and still pass the checks.

-- Page 3 of the catalogue, two books per page.
SELECT title, author
FROM books
ORDER BY title
LIMIT 2 OFFSET 4;

SELECT name, joined
FROM members
ORDER BY joined DESC
LIMIT 1 OFFSET 1;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- Page 3 of the catalogue, two books per page.
SELECT title, author
FROM books
LIMIT 2 OFFSET 2;

test.sql

-- test: Page 3 is Neuromancer and The Hobbit, then the second newest member is Linus
-- output:
--     title    |      author
-- -------------+------------------
--  Neuromancer | William Gibson
--  The Hobbit  | J. R. R. Tolkien
-- (2 rows)
--
--  name  |   joined
-- -------+------------
--  Linus | 2026-02-10
-- (1 row)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Common mistakes

LIMIT before ORDER BY

SELECT title, year
FROM books
LIMIT 3
ORDER BY year;

What psql prints

ERROR:  syntax error at or near "ORDER"

Why, and the fix

The clauses have a fixed order: SELECT, FROM, WHERE, ORDER BY, and then LIMIT and OFFSET. The rows are sorted first and the limit takes the first rows of the sorted result, so write ORDER BY year LIMIT 3.

A position that is not in the select list

SELECT title, year
FROM books
ORDER BY 3;

What psql prints

ERROR:  ORDER BY position 3 is not in select list

Why, and the fix

A number in ORDER BY is the position of a result column, counted from 1 in the select list, not in the table. This select list has two columns, so only 1 and 2 are allowed. Write the column name instead, ORDER BY year, which also keeps working when someone adds a column to the select list.

DISTINCT and a sort key that is not selected

SELECT DISTINCT genre
FROM books
ORDER BY year;

What psql prints

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

Why, and the fix

After DISTINCT, one row stands for several books: sci-fi stands for books from 1965, 1979 and 1984, so it has no single year to sort by. With DISTINCT, sort only by columns you select: ORDER BY genre.

PostgreSQL in the browser: PGlite 0.5.8 (PostgreSQL 18.3), Apache-2.0 and PostgreSQL License. Licence and source

Exit ticket

5 questions, no hints. Score 80% or more to complete the lesson.

Finish every activity above to unlock the exit ticket.

Report a problem

Spotted something wrong or unclear? Say what, and it will be checked and fixed.

#

At least 20 characters.

Only if you want a reply.

Key ideas

Without ORDER BY there is no order

A table has no built-in order, and a query without ORDER BY returns its rows in an unspecified order: often the order they were inserted, but nothing promises it, and it can change. ORDER BY year sorts ascending (ASC, the default): smallest first, and for text alphabetical. ORDER BY year DESC sorts descending. With several keys, ORDER BY genre, copies DESC, title, the second key only sorts rows that are equal in the first, the third only rows equal in both. Each key has its own direction: in ORDER BY genre, title DESC, only title is descending. Rows equal in every key come back in any order.

What you can sort by, and where NULLs go

ORDER BY accepts a column, an expression such as copies * 2, an alias from the select list, or the position of a result column: ORDER BY 2 sorts by the second column. An alias must stand alone: ORDER BY published works, ORDER BY 2026 - published fails with column "published" does not exist. A position beyond the select list fails too. NULL, a missing value, sorts as if it were larger than any other value. So Margaret, who has no city, comes last in ORDER BY city and first in ORDER BY city DESC. NULLS FIRST or NULLS LAST after a key overrides that for this key.

LIMIT and OFFSET take a slice

LIMIT 3 returns at most three rows; OFFSET 2 skips two rows before the first one it returns. With both, the skipping comes first: LIMIT 2 OFFSET 2 returns rows 3 and 4. So page n of a list with 10 rows per page is LIMIT 10 OFFSET 10 * (n - 1). LIMIT and OFFSET are PostgreSQL’s words; the SQL standard writes FETCH FIRST 3 ROWS ONLY and OFFSET 2 ROWS, which PostgreSQL also accepts. LIMIT comes after ORDER BY, and without ORDER BY you get some three rows, not the first three. The order must also be unique: when rows tie, add a key such as id or title.

Sources

Last reviewed September 30, 2026