Sorting Patterns (Keep Lists Friendly)

Problem / Context

You’re building a product list page for an e‑commerce site. PM asks for: “Sort by popularity by default; if ratings tie, show cheaper first; within each category, let users see the top 3 items; pagination must be stable.” You need clear ordering rules, a deterministic tie‑breaker, and patterns for per‑group top‑N.

Core Concept

Sorting is just deciding the order humans read rows. A good ORDER BY answers: what ties come first? what happens inside groups? can I show “top N per group” cleanly? Here are core mini-patterns.

Implementation (step by step SQL)

Setup

CREATE TABLE product (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  price NUMERIC NOT NULL,
  rating INT
);
INSERT INTO product (name, category, price, rating) VALUES
 ('Laptop','Electronics',1200,5),
 ('Smartphone','Electronics',800,4),
 ('Book A','Books',20,5),
 ('Book B','Books',15,4),
 ('Headphones','Electronics',100,3);

Quick start: to load a richer dataset (including hierarchy and comments for Top‑N), run scripts/seed_sorting.sql in your database.

1. Simple multi-column

“If categories together, then cheaper first.”

SELECT * FROM product ORDER BY category, price; -- default ASC

“If categories A→Z, inside each highest rating first.”

SELECT * FROM product ORDER BY category ASC, rating DESC;

“If show most expensive first, but break price ties by name.”

SELECT * FROM product ORDER BY price DESC, name ASC;

Takeaway: ORDER BY is a priority list; earlier columns dominate tie‑breaking.

2. Deterministic ordering

Production rule: every query that feeds pagination should end with a unique tie breaker (often primary key) so order can’t “jiggle.”

SELECT * FROM product ORDER BY price DESC, id DESC; -- stable

3. Custom sort buckets (CASE)

Put “featured” first, rest alphabetical.

SELECT * FROM product
ORDER BY (CASE WHEN rating = 5 THEN 0 ELSE 1 END), name;

4. Null handling

SELECT * FROM product ORDER BY rating DESC NULLS LAST;

Explicit NULLS FIRST/LAST keeps surprises away.

5. Hierarchy ordering (depth and name)

Adjacency list of categories:

CREATE TABLE category (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  parent_id INT REFERENCES category(id)
);
INSERT INTO category (name,parent_id) VALUES
 ('Electronics',NULL),
 ('Laptops',1),
 ('Smartphones',1),
 ('Books',NULL),
 ('Fiction',4),
 ('Non-fiction',4),
 ('Fantasy',5);

Build a tree with depth, then order: first top levels (smaller depth) then name.

WITH RECURSIVE tree AS (
  SELECT id,name,parent_id,1 AS depth, LPAD(id::text,5,'0') AS path_key
  FROM category WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id,c.name,c.parent_id,t.depth+1, t.path_key||'.'||LPAD(c.id::text,5,'0')
  FROM category c JOIN tree t ON c.parent_id=t.id
)
SELECT * FROM tree ORDER BY depth, name;

For a strict “nested order” you can ORDER BY path_key instead.

6. Top N per group (window function)

“Show top 3 highest score replies per parent comment.”

CREATE TABLE comment (
  id SERIAL PRIMARY KEY,
  parent_id INT REFERENCES comment(id),
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  score INT NOT NULL DEFAULT 0
);
INSERT INTO comment (content, score) VALUES ('First',10), ('Second',5);
INSERT INTO comment (parent_id, content, score) VALUES
 (1,'R1',7),(1,'R2',9),(1,'R3',3),(1,'R4',8),
 (2,'R1',2),(2,'R2',6);

SELECT * FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY score DESC, id) AS rn
  FROM comment WHERE parent_id IS NOT NULL
) x
WHERE rn <= 3
ORDER BY parent_id, score DESC;

ROW_NUMBER ranks inside each parent group; then we filter.

Swap ROW_NUMBER for RANK or DENSE_RANK if you want ties to behave differently.

7. Keyset pagination prep

Stable deterministic sort is required before you can switch from OFFSET/LIMIT to keyset (cursor) pagination.

SELECT * FROM product ORDER BY price DESC, id DESC LIMIT 20; -- later WHERE (price,id) < (?,?)

8. Performance hints

9. Checklist

Clear ordering = calm UIs and predictable caches.

10. User-defined ordering (WITH ORDINALITY)

Let users supply an explicit order (IDs or labels) and apply it in SQL using WITH ORDINALITY.

Example: sort products by a user-picked list of IDs

-- Desired order: [3, 5, 2]
SELECT p.*
FROM product p
JOIN unnest(ARRAY[3,5,2]) WITH ORDINALITY AS u(id, ord)
  ON u.id = p.id
ORDER BY u.ord, p.id; -- deterministic tie-breaker

Example: category preference order, then inside category by rating DESC, id DESC

-- Desired category order: ['Books','Electronics','Home']
SELECT p.*
FROM product p
LEFT JOIN unnest(ARRAY['Books','Electronics','Home']) WITH ORDINALITY AS u(category, ord)
  ON u.category = p.category
ORDER BY COALESCE(u.ord, 2147483647),  -- unmatched last
         p.rating DESC,
         p.id DESC;                    -- unique tie-breaker

Notes

Variations and Trade‑Offs

Pitfalls

Recap (Short Summary)

Define a strict, deterministic ORDER BY with explicit NULL handling and a unique final tie‑breaker. Use CASE for buckets and window functions for per‑group top‑N. Prefer keyset pagination once the order is stable.

Optional Exercises

Summary (Cheat Sheet)

References