Sorting and Grouping Techniques

📚 Schema reference · towns & counties 3 tables

fips

fips
name
state

pnw_counties

county_id
county
state
fips_short
county_seat
county_seat_town_id
year_established
origin
etymology
population_2022
land_area_sq_mi

pnw_towns

town_id
town
state
primary_county
secondary_county
tertiary_county
primary_county_id
secondary_county_id
tertiary_county_id
population_2020_census
population_2010_census
land_area_sq_mi

A query is written SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT, but SQL does not run the clauses in that order. Scroll through one query to see the order it actually uses, since it explains most “why can’t I use that alias here?” surprises.

The order SQL runs the clauses

SELECT state, COUNT(*) AS n
  FROM towns
 WHERE pop > 150000
 GROUP BY state
HAVING COUNT(*) >= 2
 ORDER BY n DESC
 LIMIT 1;

Scroll to build the diagram, one step at a time.

Step 1 · FROM runs first SQL does not start at SELECT. It starts at FROM, loading every row of towns. All 8 rows are on the table.

Step 2 · then WHERE WHERE filters rows next. pop > 150000 removes Nampa and Astoria, leaving 6 rows. WHERE runs before any grouping, so it cannot see COUNT(*).

Step 3 · then GROUP BY GROUP BY state sorts the survivors into one bucket per state: OR, WA, and ID. Each color is a group.

Step 4 · then HAVING HAVING filters groups, not rows. COUNT(*) >= 2 drops ID (only one town). This is the job WHERE could not do.

Step 5 · only now SELECT SELECT runs here, near the end, choosing state and COUNT(*) AS n. That is why an alias like n is invisible to WHERE and GROUP BY: they already ran.

Step 6 · then ORDER BY ORDER BY n DESC sorts the finished rows, so WA (3) moves ahead of OR (2). Sorting happens after SELECT, so ordering by the alias n is allowed.

Step 7 · LIMIT last LIMIT 1 is the final cut, keeping only the top row. Written first, SELECT is the fifth thing to run; the shape of your result is decided in this order, not the order you typed.

Text version of this diagram

Step 1 · FROM runs first. SQL does not start at SELECT. It starts at FROM, loading every row of towns. All 8 rows are on the table.
Step 2 · then WHERE. WHERE filters rows next. pop > 150000 removes Nampa and Astoria, leaving 6 rows. WHERE runs before any grouping, so it cannot see COUNT(*).
Step 3 · then GROUP BY. GROUP BY state sorts the survivors into one bucket per state: OR, WA, and ID. Each color is a group.
Step 4 · then HAVING. HAVING filters groups, not rows. COUNT(*) >= 2 drops ID (only one town). This is the job WHERE could not do.
Step 5 · only now SELECT. SELECT runs here, near the end, choosing state and COUNT(*) AS n. That is why an alias like n is invisible to WHERE and GROUP BY: they already ran.
Step 6 · then ORDER BY. ORDER BY n DESC sorts the finished rows, so WA (3) moves ahead of OR (2). Sorting happens after SELECT, so ordering by the alias n is allowed.
Step 7 · LIMIT last. LIMIT 1 is the final cut, keeping only the top row. Written first, SELECT is the fifth thing to run; the shape of your result is decided in this order, not the order you typed.

Sorting

ORDER BY controls how results are displayed. This is crucial for reports and identifying extremes.

Sorting 1: Rank towns by population (smallest first)

Scenario: Default ORDER BY sorts ascending (smallest to largest). Show town, state, and population_2020_census.

Sorting 2: Rank towns by population (largest first)

Scenario: DESC reverses the order for “top N” style reports. Show town, state, and population_2020_census.

With more than one column in ORDER BY, the second column only matters where the first one ties. Scroll to watch the same six rows re-sort.

ORDER BY two columns: sort, then break ties

SELECT town, state, pop
  FROM towns
 ORDER BY state, pop DESC;

Scroll to build the diagram, one step at a time.

Step 1 · no ORDER BY Without ORDER BY, rows come back in whatever order the database finds convenient. No order is promised, even if it looks stable today.

Step 2 · the first sort key ORDER BY state puts OR ahead of WA. Inside each state the rows are tied, and SQL is free to leave ties in any order: Portland is last among the Oregon rows here.

Step 3 · the second key breaks ties A second column sorts only within the ties left by the first. pop DESC orders each state from largest to smallest, and no Washington row ever moves above an Oregon row.

Step 4 · DESC belongs to one column DESC applies to the column it follows and nothing else. Each column is ascending unless it says otherwise, so reversing the states takes its own DESC.

Text version of this diagram

Step 1 · no ORDER BY. Without ORDER BY, rows come back in whatever order the database finds convenient. No order is promised, even if it looks stable today.
Step 2 · the first sort key. ORDER BY state puts OR ahead of WA. Inside each state the rows are tied, and SQL is free to leave ties in any order: Portland is last among the Oregon rows here.
Step 3 · the second key breaks ties. A second column sorts only within the ties left by the first. pop DESC orders each state from largest to smallest, and no Washington row ever moves above an Oregon row.
Step 4 · DESC belongs to one column. DESC applies to the column it follows and nothing else. Each column is ascending unless it says otherwise, so reversing the states takes its own DESC.

Sorting 3: Sort by multiple columns (state, then population within state)

Scenario: Create a report organized by state, with largest cities first within each state. Show town, state, and population_2020_census.

Sorting 4: Sort counties by age (oldest first)

Scenario: Historical timeline of county formation. Show each county with its state, year_established, and etymology.

Grouping

GROUP BY collapses rows into groups for aggregate calculations. This is one of SQL’s most powerful features.

GROUP BY: many rows collapse to one per group

SELECT state, SUM(amount)
  FROM sales
 GROUP BY state
HAVING SUM(amount) >= 100;

Scroll to build the diagram, one step at a time.

Step 1 · the rows Six sales rows, several per state. On their own, SQL has no idea which rows belong together.

Step 2 · label the groups GROUP BY state tags each row with its group. Three colors here: OR, WA, and ID.

Step 3 · collapse each group Each group collapses to one row, and SUM(amount) adds up the rows inside it: OR 120+80+60 = 260, WA 300+150 = 450, ID 40. Six rows became three.

Step 4 · HAVING filters groups HAVING runs on the grouped rows, so it can test SUM(amount). >= 100 drops ID. A plain WHERE could not do this: it runs before the groups exist.

Text version of this diagram

Step 1 · the rows. Six sales rows, several per state. On their own, SQL has no idea which rows belong together.
Step 2 · label the groups. GROUP BY state tags each row with its group. Three colors here: OR, WA, and ID.
Step 3 · collapse each group. Each group collapses to one row, and SUM(amount) adds up the rows inside it: OR 120+80+60 = 260, WA 300+150 = 450, ID 40. Six rows became three.
Step 4 · HAVING filters groups. HAVING runs on the grouped rows, so it can test SUM(amount). >= 100 drops ID. A plain WHERE could not do this: it runs before the groups exist.

Grouping 1: Count towns per state

Scenario: Basic comparison of how many incorporated towns each state has. For each state, report the count as number_of_towns.

Grouping 2: Compare states by average county population

Scenario: Which state has larger counties on average? For each state, show the count of counties as num_counties, the average county population (rounded to a whole number) as avg_county_pop, and the summed population as total_state_pop.

Grouping 3: Which counties have the most towns?

Scenario: Identify counties with many incorporated municipalities. For each primary_county and state, report the town count as num_towns and the combined town population as total_pop.

Grouping 4: Filter groups with HAVING

Scenario: Find only counties with 10 or more towns. HAVING filters after grouping (WHERE filters before). Show each primary_county and state with its town count as num_towns.

First, let’s see why WHERE doesn’t work here:

Use HAVING instead to filter grouped results:

Grouping 5: Population growth by county

Scenario: Which counties saw the most population growth in their towns from 2010 to 2020? For each primary_county and state, report summed town populations as pop_2010 and pop_2020, plus the difference as growth.