AI Hints
Homeโ€บSQL Tutorialโ€บSorting Results
SQL ยท Lesson 17 of 126

Sorting by Two Columns

Ties, and the full query order.

โฑ 2 min readโ–ถ Video at 24:14๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Helen wants a list of books grouped by genre, with the most expensive book first in each genre. That's two sorts. First by genre, then by price inside each genre.

Sorting by Two Columns
From the video: Sorting by Two Columns
  • Books grouped by genre
  • Priciest first in each genre
  • Two sorts at once

Sort, then sort again

List the columns after order by, separated by commas. SQL sorts by genre first. Then, for books in the same genre, it sorts by price, largest first. Each column can have its own direction. Run it. Business first, with The Pricing Game on top, then cooking, then fantasy.

query.sqlsql
SELECT genre, title, price
FROM books
ORDER BY genre, price DESC
LIMIT 7;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  genre   โ”‚       title       โ”‚ price โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Business โ”‚ The Pricing Game  โ”‚ 23.99 โ”‚
โ”‚ Business โ”‚ Numbers Don't Lie โ”‚ 21.99 โ”‚
โ”‚ Business โ”‚ Startup Stories   โ”‚ 19.99 โ”‚
โ”‚ Cooking  โ”‚ Spice Roads       โ”‚ 27.99 โ”‚
โ”‚ Cooking  โ”‚ The Quiet Kitchen โ”‚ 24.99 โ”‚
โ”‚ Cooking  โ”‚ One Pan Dinners   โ”‚ 21.49 โ”‚
โ”‚ Fantasy  โ”‚ The Glass Forest  โ”‚ 18.49 โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

All the parts together

Here's a query using everything so far. Books that are in stock, the most expensive first, top three. Run it. Notice the order of the parts. Select, from, where, order by, limit. That order never changes.

query.sqlsql
SELECT title, price, stock
FROM books
WHERE stock > 0
ORDER BY price DESC
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚          title           โ”‚ price โ”‚ stock โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Python for Curious Minds โ”‚ 34.99 โ”‚ 17    โ”‚
โ”‚ Data for Everyone        โ”‚ 31.99 โ”‚ 3     โ”‚
โ”‚ SQL in a Weekend         โ”‚ 29.99 โ”‚ 43    โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

The order to write it in

Here's that order again. Select, from, where, order by, limit. If you put them in a different order, you'll get an error. A good way to remember it is the sentence, some friendly whales only like seafood.

  1. SELECTcolumns
  2. FROMtable
  3. WHEREfilter
  4. ORDER BYsort
  5. LIMIThow many
The order to write it in
From the video: The order to write it in

Summary

You can sort by several columns, and you know the fixed order of the parts. Next, how to remove repeated values.

Key takeaways
  • ORDER BY a, b DESC
  • Each column has a direction
  • SELECT FROM WHERE ORDER BY LIMIT
Next up: Unique Values with DISTINCT โ€” remove duplicates