SQL ORDER BY Practice

-- BROKEN: returns duplicates or skips rows when 2+ posts share created_at.
--   Page 1: posts 100-91 (timestamps T..T-9)
--   New post arrives at T (same second as post 100)
--   Page 2: post 91 reappears because the inserted post shifted the offset
SELECT post_id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 10 OFFSET 100;

-- FIXED with deterministic tiebreaker. post_id is unique so the ordering
-- is total even when timestamps tie.
SELECT post_id, title, created_at
FROM posts
ORDER BY created_at DESC, post_id DESC
LIMIT 10 OFFSET 100;

-- BETTER, for high-traffic apps. Keyset pagination avoids OFFSET scans
-- entirely and is stable under concurrent inserts. The cursor is the
-- last (created_at, post_id) you displayed.
SELECT post_id, title, created_at
FROM posts
WHERE (created_at, post_id) < (:last_created_at, :last_post_id)
ORDER BY created_at DESC, post_id DESC
LIMIT 10;

-- Index that makes this fast:
CREATE INDEX posts_feed_idx ON posts (created_at DESC, post_id DESC);

Single-column ORDER BY plus LIMIT OFFSET is the most common pagination bug. The fix is a tiebreaker or keyset pagination.

-- Sort tickets by custom priority order, not alphabetical.
-- 'high' must come first, then 'medium', then 'low'.

-- Alphabetical sort puts them in the wrong order: high, low, medium.
SELECT ticket_id, priority FROM tickets ORDER BY priority;

-- CASE-based custom sort. Each priority gets a numeric rank.
SELECT ticket_id, priority,
       CASE priority
         WHEN 'high'   THEN 1
         WHEN 'medium' THEN 2
         WHEN 'low'    THEN 3
       END AS priority_rank
FROM tickets
ORDER BY priority_rank, created_at DESC;

-- Postgres-specific: use an array indexOf trick for the same effect.
SELECT ticket_id, priority
FROM tickets
ORDER BY array_position(ARRAY['high','medium','low'], priority),
         created_at DESC;

Alphabetical sorts low before medium. CASE assigns a numeric rank that matches the intent.