Module 1, Session 8. ORDER BY with ASC/DESC and tie-breakers, NULLS FIRST/LAST, LIMIT and OFFSET pages, keyset pagination and the order PostgreSQL really runs a query in.
Open in Present mode · 400 practice questions · Live class schedule · Try the playground
Jo class mein bola, yahan nukte mein likha hai.
What I said in class, written here as key points.
Put rows in the order people need, decide where missing values go, and hand results out one page at a time: ORDER BY, NULLS FIRST / LAST, LIMIT, OFFSET, keyset pages, and the order PostgreSQL really runs a query in.
Four pillars: the right rows, in the right order, one page at a time
ORDER BY — ASC & DESC
NULLS FIRST / LAST
LIMIT & OFFSET
WHERE
sales.orders has 1,50,000 rows. No app shows them all: every search page, feed and “top 10” sorts the rows first and then shows 20 at a time. That is today’s skill.From the right rows to the right order
WHERE, IS NULL, AND/OR with brackets, LIKE, BETWEEN, IN and DISTINCT.
ORDER BY, then pages.
OFFSET 9980 makes the database read and throw away 9,980 rows to return 20, for every page. The site slows for everyone.
SELECT product_id, product_name, price FROM products.products ORDER BY price DESC NULLS LAST, product_id LIMIT 20; -- next page: start after the last row -- shown (keyset pages, scene 9)
DESC), where the unknowns go (NULLS LAST), and a unique last column so every row has one place (product_id). Deep pages need the last row’s key, not a huge OFFSET.Each thing the search page needs maps to one piece of the query:
| What the Page Needs | SQL Clause | What PostgreSQL Does |
|---|---|---|
| 📊 Sort by price | ORDER BY price | Without ORDER BY there is no order at all; with it, the default is ASC (smallest first). |
| ⬇️ High to Low | DESC | Reverses one column. In ORDER BY a, b DESC, DESC belongs to b only. |
| ❓ Blank prices at the bottom | NULLS LAST | PostgreSQL treats NULL as bigger than every value, so DESC puts NULLs first unless you say otherwise. |
| 🔁 Same phone never twice | , product_id | A unique last column breaks every tie: each row gets exactly one place. |
| 📄 20 results per page | LIMIT 20 OFFSET 40 | LIMIT = how many rows, OFFSET = how many to skip first. This is page 3. |
| 🐢 Page 500 without the wait | WHERE id > last_id | Keyset: start after the last row you showed instead of skipping thousands. |
LIMIT and OFFSET without ORDER BY cut pages out of an order that does not exist. Same query, different rows, repeats and gaps. Always sort before you page.How PostgreSQL orders numeric values and handles NULL values in ASC and DESC directions.
Sort by one column, break ties with a second, and decide where NULLs go
Rows come back in whatever order is cheapest for PostgreSQL. Only ORDER BY promises an order. Default is ASC.
ORDER BY brand_id, price DESC: brand decides first; price only orders rows inside the same brand.
ASC puts NULLs last, DESC puts them first. Choose with NULLS FIRST / NULLS LAST.
SELECT product_id, product_name, price FROM products.products ORDER BY price DESC LIMIT 10;
SELECT product_name, brand_id, price FROM products.products ORDER BY brand_id ASC, price DESC LIMIT 10;
SELECT shipment_id, delivered_date FROM sales.shipments ORDER BY delivered_date DESC NULLS LAST LIMIT 10;
LIMIT cuts one page, OFFSET skips rows first. Click a page and watch how many rows PostgreSQL reads.
Cut a sorted result into pages, and know when OFFSET gets slow
LIMIT 20 returns at most 20 rows. On its own it gives the first 20 found, not the top 20.
LIMIT 20 OFFSET 40 skips 40 rows and returns the next 20: page 3. Always with ORDER BY.
OFFSET 1,00,000 reads and discards 1,00,000 rows. Keyset (WHERE id > last_id) starts right after the last row.
SELECT customer_id, first_name FROM customers.customers ORDER BY customer_id LIMIT 20;
SELECT customer_id, first_name FROM customers.customers ORDER BY customer_id LIMIT 20 OFFSET 40;
SELECT customer_id, first_name FROM customers.customers WHERE customer_id > 1042 ORDER BY customer_id LIMIT 20;
Time to test your knowledge! Rank top expensive products, construct page 3 of customer directories, order employees across multiple columns, and explore shipment lag.
Three rules to keep from today.
Rows have no built-in order. If the order matters (a top 10, a report, a page), write ORDER BY.
By default ASC puts NULLs last and DESC puts them first. On a DESC list people will read, write NULLS LAST.
Deep OFFSET reads and throws away every skipped row. Start after the last row shown instead: WHERE customer_id > 1042 ORDER BY customer_id LIMIT 20. Same speed on any page.
LIMIT 10 alone gave a “biggest” order of ₹3,00,772. ORDER BY net_total DESC LIMIT 10 gives the real one, ₹11,11,861. First 10 is not top 10.
The question: “Users see the same product on page 1 and again on page 2. Why, and how do you fix it?”
ORDER BY price DESC alone: two products at the same price have no fixed order. RetailMart has 13 shared prices, e.g. ₹750.98 (products 3284 and 3411). One can land on page 1 and again on page 2.
While the user reads page 1, a new row arrives at the top. Every row moves down one place, so OFFSET 20 now starts one row earlier: the last row of page 1 shows again on page 2.
End the sort with a unique column: ORDER BY price DESC, product_id. For long or live lists, start after the last row shown (WHERE customer_id > 1042) instead of counting with OFFSET.
“Two causes: ties and moving data. I end every ORDER BY with a unique column so each row has one place, and for deep or live lists I page from the last key shown instead of using OFFSET.”
You write SELECT first; PostgreSQL runs FROM first. Click each step.
You write SELECT first, but PostgreSQL runs it after WHERE
SELECT → FROM → WHERE → ORDER BY → LIMIT: readable, but not the order things happen.
FROM → WHERE → SELECT → DISTINCT → ORDER BY → LIMIT. Columns made in SELECT do not exist yet in WHERE.
An alias fails in WHERE (it runs before SELECT) and works in ORDER BY (it runs after).
-- ERROR on purpose: WHERE runs before SELECT SELECT product_name, price * 1.18 AS price_with_gst FROM products.products WHERE price_with_gst > 50000 LIMIT 10;
SELECT product_name, price * 1.18 AS price_with_gst FROM products.products WHERE price * 1.18 > 50000 LIMIT 10;
SELECT product_name, price * 1.18 AS price_with_gst FROM products.products ORDER BY price_with_gst DESC LIMIT 10;
Only an error would warn you, and none of these gives one. Each just returns the wrong rows.
SELECT order_id, net_total FROM sales.orders LIMIT 10: the biggest of those 10 is ₹3,00,772. The real biggest order is ₹11,11,861.
ORDER BY net_total DESC LIMIT 10.ORDER BY delivered_date DESC LIMIT 10 returns 10 empty dates: all 14,982 blank rows come before any real date.
ORDER BY delivered_date DESC NULLS LAST.ORDER BY brand_id, price DESC still starts with brand 1. DESC belongs to price alone.
ORDER BY brand_id DESC, price DESC starts with brand 117.For page 3, LIMIT 20 OFFSET 3 returns customers 4 to 23.
OFFSET 40.₹750.98 is the price of two products (3284 and 3411). Sorted only by price, either can come first, on any page.
ORDER BY price DESC, product_id.LIMIT 20 OFFSET 20 with no ORDER BY cuts page 2 out of an order that does not exist: repeats and gaps.
ORDER BY a unique column before paging.How you look at a new table on day one: filter it, sort it, keep it small.
Which customers have their default address in Mumbai, Delhi or Bengaluru? 2,213 do. Sort by customer_id and look at 10 first (query below). (Adding the loyalty tier from customers.customers needs a JOIN: Module 2.)
Session 7’s 5 stores in Noida or Prayagraj over 3,000 sq ft, now with ORDER BY square_ft DESC: store 15 in Prayagraj (4,858 sq ft) comes first.
ORDER BY delivered_date NULLS FIRST puts the 14,982 blank dates on top, so the missing ones are the first thing you see.
SELECT customer_id, city, state, pincode FROM customers.addresses WHERE is_default = TRUE AND city IN ('Mumbai', 'Delhi', 'Bengaluru') ORDER BY customer_id ASC LIMIT 10;
Questions interviewers really ask about today’s topic, with the answer and the trap.
Close your notes. Answer from memory. Click any card to reveal the verified answer.
ORDER BY promises an order.ORDER BY price DESC NULLS LAST to move them to the end.brand_id (ascending, the default). price DESC only orders rows inside the same brand. DESC applies to price alone.WHERE id > last_id) avoids that.ORDER BY it works, because sorting runs after SELECT.The rules to remember from today, for every sorted list and every page you build.
Rows have no built-in order. If the result must be sorted (a report, a top-10, a page), write ORDER BY, every time.
ORDER BY price DESC, product_id: when two prices are equal, the id decides. Each row gets exactly one place, so pages never overlap.
ASC puts NULLs last, DESC puts them first. Say what you want with NULLS FIRST or NULLS LAST.
Deep OFFSET reads and discards every skipped row. For long lists, page with WHERE id > last_id ORDER BY id LIMIT 20.
WHERE runs before SELECT makes the aliases, so an alias fails in WHERE and works in ORDER BY.
Add LIMIT 10 while you look around a table: sales.orders alone has 1,50,000 rows. And never use OFFSET without ORDER BY.
Every pattern from today as a real RetailMart query: run it, copy it, change it.
SELECT product_id, product_name, price FROM products.products ORDER BY price DESC, product_id LIMIT 10;
SELECT product_name, brand_id, price FROM products.products ORDER BY brand_id ASC, price DESC NULLS LAST LIMIT 10;
SELECT shipment_id, delivered_date FROM sales.shipments ORDER BY delivered_date NULLS FIRST LIMIT 10;
SELECT product_name, price * 1.18 AS price_with_gst FROM products.products ORDER BY price_with_gst DESC LIMIT 10;
SELECT order_id, order_date, net_total FROM sales.orders WHERE order_status = 'Delivered' ORDER BY net_total DESC LIMIT 5;
SELECT customer_id, first_name FROM customers.customers ORDER BY customer_id LIMIT 20 OFFSET 40;
SELECT customer_id, first_name FROM customers.customers WHERE customer_id > 1042 ORDER BY customer_id LIMIT 20;
SELECT product_id, product_name, price FROM products.products ORDER BY price DESC, product_id LIMIT 20 OFFSET 20;
Do these 4 drills on RetailMart in your own PostgreSQL before the next class.
From products.products, list the 10 most expensive and the 10 cheapest products. Add product_id as the tie-breaker in both.
From sales.shipments, list the shipments with no delivered_date, oldest shipped_date first, 20 per page. Fetch page 2.
In products.products, fetch page 2 (rows 11–20) of all products priced under ₹200, sorted by price ascending with product_id as the tie-breaker.
Get page 1 of customers.customers by customer_id, 20 per page. Then get page 2 twice: with OFFSET 20, and with WHERE customer_id > the last id of page 1. Both must return the same 20 rows.
59 sessions, from zero to job-ready. Today is session 8.