Order 109 disappeared from the warehouse’s list

See the problem

The warehouse screen shows the next three orders to pack: the oldest orders that are still placed. Behind it is this query:

The warehouse list

SELECT order_id, customer_id, order_date, total
FROM orders
WHERE status = 'placed'
LIMIT 3;

For weeks it shows orders 109, 110 and 111, the oldest three. Then support corrects order 109’s total, from 13.19 to 13.20. Nothing else changes. The next time the screen loads, it shows:

order_id	customer_id	order_date	total
110	2	2026-10-14	59.00
111	5	2026-10-15	54.00
113	6	2026-10-18	49.98

Order 109, placed on 14 October, is gone. Nobody packs it, and Ben waits. A week later, a phone order typed in late with the date 12 October never shows up at all.

The query didn’t change. So why did the list?

Define the task

Write the warehouse list: orders whose status is 'placed', with order_id, customer_id, order_date and total.

• Oldest first, by order_date.
• Two orders from the same day go lower order_id first.
• Only the first three.
• The list must be the same however Postgres stores the rows: after an update, and when an older order is typed in late.

Pause and think

⏸ Pause and think

The query has no ORDER BY. In what order does Postgres give the rows? And how could one UPDATE change which three come first?

Give it two minutes with pen and paper before you open a hint.

Hints open one at a time:

Hint 1 of 3

With no ORDER BY, Postgres gives rows in whatever order is quickest. For a small table, that is the order they sit in the table on disk. It is not the order of order_id.

Hint 2 of 3

An UPDATE in Postgres writes a new copy of the row, usually at the end of the table. So order 109 moved from first to last.

Hint 3 of 3

LIMIT 3 keeps the first three rows it gets, whichever they are. Only ORDER BY says which rows come first. Sort by order_date, then by order_id for orders from the same day.

Understand the idea

You write a query in one order, and the database runs it in another. You write SELECT, FROM, WHERE, ORDER BY, LIMIT. Postgres runs FROM first and LIMIT last:

Runs	Clause	What it does
1	FROM orders	Reads the table’s rows
2	WHERE status = 'placed'	Keeps the rows where the condition is true
3	SELECT order_id, order_date	Picks the columns, and can work out new ones
4	ORDER BY order_date, order_id	Sorts the rows. Smallest first (ASC) unless you say DESC
5	LIMIT 3	Keeps the first 3 rows and stops

That order explains two rules. ORDER BY can use a name you made in SELECT on its own (total * 2 AS doubled, then ORDER BY doubled, but not ORDER BY doubled + 1), because it runs after SELECT. WHERE can’t, because it runs before.

Rows have no order until ORDER BY gives them one. A table is a set of rows, not a list. With no ORDER BY, Postgres gives rows in whatever order is quickest. For a small table that’s usually the order they sit on disk, which isn’t order_id order:

• An UPDATE writes a new copy of the row, usually at the end. The old copy is cleaned up later.
• A deleted row leaves a gap, and a new row can fill it.
• On a big table, an index or several workers reading at once can change the order again.

So a query that “always” came back sorted can change any day, with no change to the query.

LIMIT only stops early. It keeps the first n rows, whichever they are. After ORDER BY, that’s the top n. Without it, it’s any n.

A sort needs a tie-breaker. ORDER BY order_date says nothing about two orders from the same day. Postgres may give 109 before 110 today, and 110 before 109 after an update. Add a column that is different for every row, like the primary key: ORDER BY order_date, order_id. Now every row has exactly one place.

Watch the warehouse query run on the orders after the update, one step at a time. Then type your own rows, in the order you want them stored:

The warehouse list, step by step

⤢ Full view

1. FROM orders
2. WHERE status = 'placed'
3. SELECT order_id, order_date
4. ORDER BY order_date, order_id
5. LIMIT 3

orders

order_id	order_date	status
108	2026-10-11	shipped
110	2026-10-14	placed
111	2026-10-15	placed
112	2026-10-16	delivered
113	2026-10-18	placed
114	2026-10-20	placed
109	2026-10-14	placed

Step 1 of 7. FROM orders reads all 7 rows, in the order they’re stored. That isn’t order_id order: a changed row is written again at the end.

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT order_id, order_date
2FROM orders
3WHERE status = 'placed'
4ORDER BY order_date, order_id
5LIMIT 3;

Try your own input

Orders as stored: order_id, order_date, status108, 2026-10-11, shipped 110, 2026-10-14, placed 111, 2026-10-15, placed 112, 2026-10-16, delivered 113, 2026-10-18, placed 114, 2026-10-20, placed 109, 2026-10-14, placedShow it

WHERE keeps a row only when its condition is true.

• Compare with =, <> (not equal), <, <=, > and >=. Text compares letter by letter, and capitals count: 'Placed' isn’t 'placed'.
• Join conditions with AND, OR and NOT. AND goes first, like × before +, so add brackets when you mix them.
• status IN ('placed', 'shipped') is a short way to write two = joined by OR.
• total BETWEEN 10 AND 20 includes both ends.
• email LIKE '%@example.com' matches a pattern: % is any run of characters, and _ is exactly one.
• = NULL never matches. Use IS NULL. The lesson on NULL shows why.

SELECT picks the columns. SELECT * gives every column, which is useful for a quick look. In an app, name the columns, so a new column doesn’t change what the app gets. AS gives a column a new name: SELECT total AS amount.

Climb the ladder

All three keep the placed orders and take three. What changes is which three, and in what order.

Okay

No ORDER BY

The idea: Keep the placed orders, then take the first three.

It looks right because the store’s rows were added oldest first, so they sit on disk in date order.

SELECT order_id, customer_id, order_date, total
FROM orders
WHERE status = 'placed'
LIMIT 3;

Gets right

The store’s data, by luck

Gets wrong

Any change to the order rows are stored in

Why

Without ORDER BY, LIMIT keeps the first three rows Postgres happens to read.

Where it breaks: after the update, order 109 sits last, so the list is 110, 111 and 113. A late phone order dated 12 October sits last too, so it never makes the list. Okay passes 1 of the 3 checks below.

Better

ORDER BY order_date

The idea: Sort the placed orders oldest first, then take three.

The gap in Okay: nothing said which rows come first.

Now the late phone order comes first, and order 109 is back on the list.

Highlighted lines are new in this rung.

SELECT order_id, customer_id, order_date, total
FROM orders
WHERE status = 'placed'
ORDER BY order_date
LIMIT 3;

Gets right

Which days make the listwas The store’s data, by luck

Gets wrong

Orders from the same day, in either order

Why

109 and 110 are both from 14 October, so ORDER BY order_date lets Postgres put either first.

What changed from the previous rung

SELECT order_id, customer_id, order_date, total
FROM orders
WHERE status = 'placed'
ORDER BY order_date
LIMIT 3;

Where it breaks: after the update, Postgres gives 110 before 109, so the pickers pack them in the wrong order. And when a tie falls on third place, which order makes the list at all is up to Postgres. Better passes 2 of 3.

Best

ORDER BY order_date, order_id

The idea: Sort by date, and break ties with order_id, so every row has exactly one place.

The gap in Better: the sort had ties.

Highlighted lines are new in this rung.

SELECT order_id, customer_id, order_date, total
FROM orders
WHERE status = 'placed'
ORDER BY order_date, order_id
LIMIT 3;

Gets right

Every check, however the rows are storedwas Which days make the list

Gets wrong

Nothing the checks try

Why

order_id is the primary key, so no two rows tie.

What changed from the previous rung

SELECT order_id, customer_id, order_date, total
FROM orders
WHERE status = 'placed'
ORDER BY order_date
ORDER BY order_date, order_id
LIMIT 3;

When Best isn’t best: to keep three rows, Postgres still sorts every placed order. That’s fine for a few thousand. For millions, an index on (order_date, order_id) lets Postgres read the oldest rows already in order and stop after three. The query stays the same; the lesson on indexes shows how.

Write it yourself

Now write it in the page. Press Run query to see your result and check it. Postgres runs in your browser, so nothing is sent anywhere. Each check changes a few rows first, then they’re put back.

Try Okay from the ladder first, and open a failed check to see what it changed. Then press Show the steps to watch your own query run.

The warehouse list

Ctrl + Enter runs

The tables

• orders (order_id, customer_id, order_date, status, total)

-- The 3 oldest placed orders. Same day: lower order_id first. SELECT order_id, customer_id, order_date, total FROM orders WHERE status = 'placed';

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data (row order matters)
2. ○Not run yet: A phone order is typed in late, dated 12 October (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 3, '2026-10-12', 'placed', 20.00);

3. ○Not run yet: Support corrects order 109’s total (row order matters)
   What this check changes

   UPDATE orders SET total = 13.20 WHERE order_id = 109;

Each check runs on its own copy of the data, so your query can’t change the real tables.

Compare

Way	Gets right	Gets wrong	Use it when
Okay: no ORDER BY	Nothing it can promise	Which rows, and their order	Never with LIMIT; fine for a quick look at a table
Better: ORDER BY order_date	Which days make the list	Ties: their order, and who makes the cut	The column is already unique
Best: ORDER BY order_date, order_id	Every row has one place	Nothing the checks try	Always, with LIMIT or paging

Common mistakes and edge cases

!AND goes before OR

WHERE category = 'stationery' OR category = 'home' AND price < 10 means stationery at any price, or home under 10. Put brackets around the OR, or use category IN ('stationery', 'home'). Practice problem 1 meets this.

• A row in your result isn’t proof of an order. A test on a small table can pass for years with no ORDER BY. Write ORDER BY whenever order matters.
• DESC belongs to one column. ORDER BY signed_up_on DESC, customer_id sorts the dates newest first, but the ids smallest first. Write DESC after each column you want reversed.
• OFFSET reads and throws away. LIMIT 20 OFFSET 10000 still reads 10,020 rows. And a row added while someone reads page 1 pushes a row from page 1 onto page 2. Practice problem 2 meets this.
• NULL sorts last in ASC and first in DESC. Say NULLS FIRST or NULLS LAST to choose.
• Capitals matter in text. 'Placed', 'placed ' with a space, and 'placed' are three different values. Compare lower(email) when case shouldn’t matter.
• _ and % in a LIKE pattern are wildcards. '%_pro%' matches any character before “pro”. Write '%\_pro%' to mean a real underscore.
• ORDER BY 2 sorts by the second column of SELECT. It works, but it breaks quietly when someone adds a column. Name the column instead.

Say it in the interview

Questions to ask first

• Oldest first by which date: when the order was placed, or when it was paid?
• Two orders from the same day: does the order between them matter?
• Is this one screen, or a list people page through?
• How many placed orders can there be at once?

A 60-second walkthrough

“I’d filter to status = ‘placed’, then ORDER BY order_date, order_id, and LIMIT 3. ORDER BY matters: without it, Postgres can return rows in any order, and in practice the order changes after updates, so LIMIT would keep a random three. The second sort key, order_id, breaks ties between orders from the same day. So the list is the same every time, and nobody’s order is skipped. If the table grows large, I’d add an index on order_date and order_id. Then the database reads the first three in order instead of sorting everything.”

Likely follow-ups

• What order does SQL run a query in? FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
• Why can ORDER BY use a SELECT alias but WHERE can’t? ORDER BY runs after SELECT, and WHERE runs before it.
• How do you page through a big table? Not with a big OFFSET. Remember the last row’s sort values and ask for rows after them: WHERE (signed_up_on, customer_id) < (...). That’s keyset paging.
• Is IN faster than OR? Not so you’d notice: Postgres turns IN (...) into = ANY (...), one check against a list. Pick IN because it’s easier to read.

Practice

Three problems, from easy to medium. Each has hints, checks you can run here, and its own Okay, Better and Best answers. Try before you open them.

1. Cheap stationery and home products (easy)

The “gifts under 10” page lists products in the stationery or home category that cost less than 10. Show product_id, name, category and price, cheapest first. Two products with the same price go lower product_id first.

Hint 1 of 3

Two conditions: the category is one of two, and the price is under 10. Both must be true.

Hint 2 of 3

AND runs before OR. Without brackets, the price rule only applies to home.

Hint 3 of 3

category IN (...) AND price < 10, then ORDER BY price, product_id.

Cheap stationery and home products

Ctrl + Enter runs

The tables

• products (product_id, name, category, price)

SELECT product_id, name, category, price FROM products;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data (row order matters)
2. ○Not run yet: A 35.00 fountain pen joins stationery (row order matters)
   What this check changes

   INSERT INTO products (product_id, name, category, price)
   VALUES (9, 'Fountain pen', 'stationery', 35.00);

3. ○Not run yet: Notebooks go on sale at 3.20, the same price as the gel pens (row order matters)
   What this check changes

   UPDATE products SET price = 3.20 WHERE product_id = 1;

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

Okay

OR and AND with no brackets

The idea: Write the conditions as you’d say them.

Okay passes 2 of 3. A 35.00 fountain pen shows up on the gifts under 10 page.

SELECT product_id, name, category, price
FROM products
WHERE category = 'stationery' OR category = 'home' AND price < 10
ORDER BY price, product_id;

Gets right

The store’s data: every stationery item is cheap

Gets wrong

Stationery at 10 or more

Why

AND runs first, so this is stationery at any price, or home under 10.

Better

Brackets, but no tie-breaker

The idea: Brackets make the OR happen first.

The gap in Okay: the price rule only covered home.

Highlighted lines are new in this rung.

SELECT product_id, name, category, price
FROM products
WHERE (category = 'stationery' OR category = 'home') AND price < 10
ORDER BY price;

Gets right

Which products make the page

Gets wrong

Two products at the same price, in either order

Why

ORDER BY price alone leaves ties in any order.

What changed from the previous rung

SELECT product_id, name, category, price
FROM products
WHERE category = 'stationery' OR category = 'home' AND price < 10
ORDER BY price, product_id;
WHERE (category = 'stationery' OR category = 'home') AND price < 10
ORDER BY price;

Where it breaks: when notebooks go on sale at 3.20, the gel pens’ price, Postgres puts the gel pens first. Better passes 2 of 3.

Best

IN, and a tie-breaker

The idea: IN says one of these, with no brackets to forget, and product_id breaks ties.

The gap in Better: ties at the same price.

Highlighted lines are new in this rung.

SELECT product_id, name, category, price
FROM products
WHERE category IN ('stationery', 'home') AND price < 10
ORDER BY price, product_id;

Gets right

Every check

Gets wrong

Nothing the checks try

Why

IN can’t mix with AND the wrong way, and product_id is unique.

What changed from the previous rung

SELECT product_id, name, category, price
FROM products
WHERE (category = 'stationery' OR category = 'home') AND price < 10
ORDER BY price;
WHERE category IN ('stationery', 'home') AND price < 10
ORDER BY price, product_id;

When Best isn’t best: if shoppers pick the sort (“price, high to low”), build the ORDER BY from a fixed list of choices, and keep product_id last in each.

2. The next page of customers (medium)

The admin screen lists customers, newest sign-up first, three to a page. Two customers who signed up on the same day go higher customer_id first. Page 1 showed Hugo, Grace and Farid; the last row was Farid, who signed up on 2026-05-02 and is customer 6. Show page 2: customer_id, name and signed_up_on. It must start right after Farid, even if someone signs up while page 1 is on screen.

Hint 1 of 3

Newest first is ORDER BY signed_up_on DESC. Which column breaks a tie, and which way?

Hint 2 of 3

OFFSET 3 skips three rows. If a new customer signs up, which three are those now?

Hint 3 of 3

Ask for the rows that sort after Farid: WHERE (signed_up_on, customer_id) < (Farid’s date, Farid’s id), with the same ORDER BY, then LIMIT 3.

The next page of customers

Ctrl + Enter runs

The tables

• customers (customer_id, name, email, city, signed_up_on)

SELECT customer_id, name, signed_up_on FROM customers;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data (row order matters)
2. ○Not run yet: Ben’s sign-up date is corrected to 20 February, the same day as Chen (row order matters)
   What this check changes

   UPDATE customers SET signed_up_on = '2026-02-20' WHERE customer_id = 2;

3. ○Not run yet: Jon signs up while page 1 is on screen (row order matters)
   What this check changes

   INSERT INTO customers (customer_id, name, email, city, signed_up_on)
   VALUES (9, 'Jon Berg', 'jon@example.com', 'Oslo', '2026-11-07');

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

Okay

OFFSET, with ties

The idea: Sort newest first, skip page 1, take three.

Okay passes 1 of 3. When Ben’s date is corrected to Chen’s, Postgres puts Ben on page 2 instead of Chen.

SELECT customer_id, name, signed_up_on
FROM customers
ORDER BY signed_up_on DESC
LIMIT 3 OFFSET 3;

Gets right

The store’s data: no two sign-ups share a day

Gets wrong

A tie at the end of the page; a new sign-up

Why

With a tie at the cut, either customer can land on page 2.

Better

OFFSET, with a tie-breaker

The idea: Add customer_id DESC, so every customer has one place.

The gap in Okay: ties at the cut.

Highlighted lines are new in this rung.

SELECT customer_id, name, signed_up_on
FROM customers
ORDER BY signed_up_on DESC, customer_id DESC
LIMIT 3 OFFSET 3;

Gets right

Ties

Gets wrong

Anyone who signs up while page 1 is on screen

Why

OFFSET counts rows, and a new row at the top shifts every row down one.

What changed from the previous rung

SELECT customer_id, name, signed_up_on
FROM customers
ORDER BY signed_up_on DESC
ORDER BY signed_up_on DESC, customer_id DESC
LIMIT 3 OFFSET 3;

Where it breaks: Jon signs up while page 1 is on screen. Jon is now the newest, so OFFSET 3 skips Jon, Hugo and Grace, and Farid shows up again on page 2. Better passes 2 of 3.

Best

Start after the last row (keyset paging)

The idea: Remember the last row’s date and id, and ask for the rows that sort after it.

The gap in Better: the page started at a count, not at a row.

It’s faster too: with an index on (signed_up_on, customer_id), Postgres jumps straight to Farid, where OFFSET 10000 would read and throw away 10,000 rows.

Highlighted lines are new in this rung.

SELECT customer_id, name, signed_up_on
FROM customers
WHERE (signed_up_on, customer_id) < ('2026-05-02', 6)
ORDER BY signed_up_on DESC, customer_id DESC
LIMIT 3;

Gets right

Ties, and new sign-ups

Gets wrong

Nothing the checks try

Why

(a, b) < (x, y) compares a first, and b only when a ties, just like the ORDER BY.

What changed from the previous rung

SELECT customer_id, name, signed_up_on
FROM customers
WHERE (signed_up_on, customer_id) < ('2026-05-02', 6)
ORDER BY signed_up_on DESC, customer_id DESC
LIMIT 3 OFFSET 3;
LIMIT 3;

When Best isn’t best: keyset paging can’t jump to page 50; it only goes to the next page. When people need page numbers on a small list, OFFSET with a tie-breaker is fine.

3. Customers with an example.com email (medium)

Find the customers whose email is at the domain example.com, whatever the capitals. Show customer_id, name and email, by customer_id. An address like ivy@notexample.com isn’t at example.com.

Hint 1 of 3

LIKE matches a pattern: % is any run of characters. Where must the pattern start, so notexample.com doesn’t match?

Hint 2 of 3

Put the @ in the pattern: %@example.com.

Hint 3 of 3

LIKE cares about capitals. Compare lower(email) instead.

Customers with an example.com email

Ctrl + Enter runs

The tables

• customers (customer_id, name, email, city, signed_up_on)

SELECT customer_id, name, email FROM customers ORDER BY customer_id;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data (row order matters)
2. ○Not run yet: Ivy signs up as ivy@notexample.com (row order matters)
   What this check changes

   INSERT INTO customers (customer_id, name, email, city, signed_up_on)
   VALUES (9, 'Ivy Nair', 'ivy@notexample.com', 'Kochi', '2026-08-01');

3. ○Not run yet: Jon’s email is saved in capitals: JON@EXAMPLE.COM (row order matters)
   What this check changes

   INSERT INTO customers (customer_id, name, email, city, signed_up_on)
   VALUES (9, 'Jon Berg', 'JON@EXAMPLE.COM', 'Oslo', '2026-08-02');

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

Okay

Ends with example.com

The idea: LIKE '%example.com': anything, then example.com.

Okay passes 1 of 3. Ivy, at notexample.com, is counted, and Jon, at EXAMPLE.COM, is missed.

SELECT customer_id, name, email
FROM customers
WHERE email LIKE '%example.com'
ORDER BY customer_id;

Gets right

The store’s data

Gets wrong

notexample.com, and capitals

Why

% matches any characters, including the “not” in notexample.com.

Better

Ends with @example.com

The idea: Put the @ in the pattern, so the domain must be exactly example.com.

The gap in Okay: the pattern didn’t say where the domain starts.

Highlighted lines are new in this rung.

SELECT customer_id, name, email
FROM customers
WHERE email LIKE '%@example.com'
ORDER BY customer_id;

Gets right

Other domains that end the same way

Gets wrong

Capitals

Why

LIKE compares letters exactly, so E isn’t e.

What changed from the previous rung

SELECT customer_id, name, email
FROM customers
WHERE email LIKE '%example.com'
WHERE email LIKE '%@example.com'
ORDER BY customer_id;

Where it breaks: Jon’s email is saved as JON@EXAMPLE.COM, and LIKE '%@example.com' misses it. Better passes 2 of 3.

Best

lower(email)

The idea: Make the email small letters first, then match.

The gap in Better: capitals.

email ILIKE '%@example.com' does the same in Postgres. ILIKE isn’t standard SQL; lower() works in every database.

Highlighted lines are new in this rung.

SELECT customer_id, name, email
FROM customers
WHERE lower(email) LIKE '%@example.com'
ORDER BY customer_id;

Gets right

Every check

Gets wrong

Nothing the checks try

Why

lower() turns JON@EXAMPLE.COM into jon@example.com before LIKE compares.

What changed from the previous rung

SELECT customer_id, name, email
FROM customers
WHERE email LIKE '%@example.com'
WHERE lower(email) LIKE '%@example.com'
ORDER BY customer_id;

When Best isn’t best: a pattern that starts with % can’t use a plain index, so Postgres reads every customer. If the app often searches by domain, store the domain in its own column, saved in small letters.

Check yourself

A few quick questions. Ones you miss are remembered, so you can find your weak spots later.

1. Predict the outputorders holds three rows, stored in this order. Order 109 was updated last. What does this query return?

   -- stored: 110, 111, 109
   SELECT order_id FROM orders LIMIT 2;

   110 and 111, always109 and 110, always: the lowest idsAny two of them: nothing says which come firstAn error: LIMIT needs ORDER BY

   Check answer
2. Multiple choiceIn what order does Postgres run a query’s clauses?

   SELECT, FROM, WHERE, ORDER BY, LIMITFROM, WHERE, SELECT, ORDER BY, LIMITFROM, SELECT, WHERE, LIMIT, ORDER BYFROM, WHERE, ORDER BY, LIMIT, SELECT

   Check answer
3. Spot the bugThis should list products under 10 in stationery or home. Which product wrongly shows up?

   SELECT name, category, price
   FROM products
   WHERE category = 'stationery' OR category = 'home' AND price < 10;

   A 35.00 fountain pen in stationeryA 12.00 water bottle in homeA 3.20 gel pen in stationeryA 9.99 cable in electronics

   Check answer
4. Multiple choiceThe query ends ORDER BY order_date LIMIT 3. Orders 109 and 110 are from the same day. What can happen?

   109 always comes first: it has the lower id110 always comes first: it was stored firstEither can come first, and it can change from one run to the nextPostgres stops with an error about the tie

   Check answer
5. Multiple choiceCustomers are listed newest first, 3 to a page, and page 2 is LIMIT 3 OFFSET 3. Someone new signs up while you read page 1. What does page 2 show?

   The next 3 customers, as plannedThe last customer from page 1 again, then 2 new onesThe next 3, but one customer is skippedAn error, because the table changed

   Check answer

Flashcards

Add these to your review deck. Each card comes back a little later every time you get it right.

• Card 1In what order does Postgres run SELECT, FROM, WHERE, ORDER BY and LIMIT?Show answer
• Card 2What order do rows come back in with no ORDER BY?Show answer
• Card 3Why add a second column to ORDER BY?Show answer
• Card 4Which runs first, AND or OR?Show answer
• Card 5What do % and _ mean in LIKE?Show answer
• Card 6Why page with WHERE (a, b) < (last a, last b) instead of OFFSET?Show answer

Add 6 cards to my review deck

Cheat sheet

• Written: SELECT, FROM, WHERE, ORDER BY, LIMIT. Run: FROM, WHERE, SELECT, ORDER BY, LIMIT.
• No ORDER BY means no promised order, and it can change after any update.
• LIMIT n keeps the first n rows. Use it with ORDER BY, and break ties with a unique column.
• DESC reverses one column. NULL sorts last in ASC, first in DESC.
• AND before OR: use brackets or IN. BETWEEN includes both ends.
• LIKE: % is any run of characters, _ is one. Capitals count; compare lower(col).
• Page with WHERE (a, b) < (last a, last b), not a big OFFSET.

Go deeper

• In the PostgreSQL docs: SELECT, sorting rows, LIMIT and OFFSET and pattern matching.
• Try your own rows: the warehouse list on the SQL visualizer.
• Next on the SQL roadmap: the whole map, from your first SELECT to window functions.

Can you do these now?

If you’re not sure of any of these, scroll back to that part before moving on.

• Write SELECT, FROM, WHERE, ORDER BY and LIMIT, and say the order the database runs them in
• Say why rows come back in no fixed order without ORDER BY
• Break ties in ORDER BY, so LIMIT and paging give the same rows every time
• Combine AND, OR and IN without the precedence trap, and match text with LIKE

✓Mark as completeI already know this

Status: Not started