Skip to main content
CodeSparring

LIMIT and DISTINCT

Level 1: Level 1: SQL Foundations (Reading Source Data)easy11 minLIMITOFFSETDISTINCTdistinct on multiple columns

Sample the top rows and collapse duplicates during exploration.

Two profiling reflexes

When a fresh source lands, a DE does two things immediately:

  1. Sample it: LIMIT 10 after an ORDER BY to eyeball the top rows without pulling millions.
  2. Probe cardinality: SELECT DISTINCT status FROM orders to learn what values a column actually contains (often not what the schema doc claims).

LIMIT (and OFFSET)

LIMIT n returns at most n rows; LIMIT n OFFSET m skips m then returns n (basic pagination). Always pair LIMIT with ORDER BY. A limit on an unsorted set gives arbitrary rows.

DISTINCT

DISTINCT removes duplicate rows from the result. SELECT DISTINCT region, status FROM orders returns each unique combination of the two columns, a fast way to map the value space.

Worked example:

SELECT DISTINCT status
FROM orders
ORDER BY status;

Anatomy:

SELECT DISTINCT region, status   -- unique (region, status) pairs
FROM orders
ORDER BY region, status
LIMIT 10 OFFSET 0;               -- top 10 after sorting (OFFSET optional)

In the warehouse (dialect note). SQLite/Postgres/MySQL use LIMIT. SQL Server uses SELECT TOP 10 ... or the ANSI OFFSET ... FETCH NEXT 10 ROWS ONLY; Oracle also uses FETCH FIRST. LIMIT is the portable choice for this course but flag it when you move to SQL Server.

Pitfall. DISTINCT applies to the entire row, not one column: SELECT DISTINCT region, status does not mean "distinct regions with any status." And LIMIT without ORDER BY is non-deterministic.

Recap. LIMIT/OFFSET sample a sorted set; DISTINCT collapses duplicate rows (across all selected columns) to profile a source's real value space.

Sample data for this example
CREATE TABLE orders (
  order_id INTEGER,
  status   TEXT
);
INSERT INTO orders VALUES
  (1, 'paid'),
  (2, 'shipped'),
  (3, 'paid'),
  (4, 'cancelled'),
  (5, 'shipped'),
  (6, 'paid');
Worked example (SQL)
SELECT DISTINCT status
FROM orders
ORDER BY status;

Apply

Your turn

The task this lesson builds to.

Return the distinct list of order statuses actually present in the source, sorted ascending. One column: status.

3 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, so it survives past today.

Profile the raw table: return the distinct (region, status) combinations present, sorted by region ascending then status ascending, and take only the top 10 with LIMIT. Columns: region, status.

3 hints and 1 automated check are waiting in the workspace.