SELECT in PostgreSQL

Postgres SELECT aligns with ANSI SQL from SQL track plus dialect functions: DISTINCT ON, casting with ::, and powerful string/date libraries.

DISTINCT ON

SELECT DISTINCT ON (customer_id)
  customer_id, id AS latest_order_id, created_at
FROM orders
ORDER BY customer_id, created_at DESC;

Picks the first row per group after ORDER BY—Postgres-specific pattern for latest-per-group without window functions.

Practice: Seed customers/orders tables from earlier lessons, then run queries in psql or DB Fiddle (PostgreSQL).

Casting and COALESCE

SELECT
  id,
  total::TEXT AS total_text,
  COALESCE(notes, 'none') AS notes_display
FROM orders;

Limit and pagination

SELECT id, total FROM orders
ORDER BY id
LIMIT 20 OFFSET 40;

For large offsets prefer keyset pagination (WHERE id > $last)—OFFSET scans skipped rows.

Important interview questions and answers

1. Q: DISTINCT ON requirement?
   A: ORDER BY must start with the DISTINCT ON expressions.
2. Q: :: cast vs CAST()?
   A: Both work; :: is idiomatic in Postgres.

Self-check

1. How do you fetch the latest order per customer with DISTINCT ON?
2. Why is OFFSET expensive at scale?

Tip: DISTINCT ON requires ORDER BY to start with the same expressions—easy interview trap.