ORDER BY

Sorting and the top 3

SQLite in your browser
Lesson concept What you'll do
1 / 5

Find the top 3 books: the longest, or the oldest.

ORDER BY sorts (add DESC for largest first) and LIMIT keeps the first few rows.

The database
9 rows
book_idtitleauthorgenreyearpages
1Pride and PrejudiceJane AustenRomance1813432
2The Great GatsbyF. Scott FitzgeraldFiction1925180
3DraculaBram StokerHorror1897418
4MockingjaySuzanne CollinsScience Fiction2010390
5The Time MachineH.G. WellsScience Fiction1895118
6Angels & DemonsDan BrownThriller2000616
7Charlotte's WebE.B. WhiteChildren1952184
8The Fault in Our StarsJohn GreenRomance2012313
9The Fellowship of the RingJ.R.R. TolkienFantasy1954423
Click a name to type it
Your query
Hint: click the green edit button on the board, or type it yourself
Query output Ready to run

Run your query to see the result table.

Press the green Run button or Ctrl+Enter.

Study notes

How ORDER BY, DESC, LIMIT works

Written order vs the order SQL works in

YOU WRITE

  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY
  7. LIMIT

SQL WORKS THROUGH IT

  1. 1. FROM take the table
  2. 2. WHERE keep some rows
  3. 3. GROUP BY make piles
  4. 4. HAVING keep some piles
  5. 5. SELECT pick and work out columns
  6. 6. ORDER BY sort
  7. 7. LIMIT keep the first few

ORDER BY and LIMIT come last, after SELECT. That is why ORDER BY can use an AS name everywhere, while WHERE can't in most databases.

  • How does ORDER BY sort?

    ORDER BY pages sorts the result by pages, smallest first. Text sorts A to Z. Add DESC (descending) for largest first, or Z to A.

  • What does LIMIT do?

    LIMIT 3 keeps the first 3 rows of the result. Together with ORDER BY it answers "top 3" and "bottom 3" questions.

  • Does the order of the parts matter?

    Yes. The parts always go in this order: SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT.

  • Is the order of rows fixed without ORDER BY?

    No. Without ORDER BY a database can return rows in any order, and it may change. If the order matters, always sort.

  • How is this like Excel?

    =TAKE(SORT(B2:F11, 5, -1), 3) does the same: sort by the fifth column, largest first, and keep 3 rows.

Other databases

How other databases do it

The lessons run on SQLite. Most of SQL is the same everywhere; these are the places where this lesson's SQL differs.

Keeping the first 3 rows
DatabaseHowNote
SQLite (these lessons)ORDER BY pages DESC LIMIT 3
PostgreSQLORDER BY pages DESC LIMIT 3The standard FETCH FIRST 3 ROWS ONLY works too.
MySQL / MariaDBORDER BY pages DESC LIMIT 3
SQL ServerSELECT TOP 3 title, pages FROM ... ORDER BY pages DESCOr OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY after ORDER BY (SQL Server 2012 and later).
OracleORDER BY pages DESC FETCH FIRST 3 ROWS ONLYOracle 12c and later. Older versions filter on ROWNUM around a sorted subquery.
Sybase ASESELECT TOP 3 title, pages FROM ... ORDER BY pages DESCOr SET ROWCOUNT 3 before the query.
Where empty (NULL) values go when sorting smallest first
DatabaseHowNote
SQLite (these lessons)FirstWrite NULLS LAST to move them.
PostgreSQLLastNULL counts as larger than any value. NULLS FIRST / NULLS LAST change it.
MySQL / MariaDBFirstNo NULLS LAST: sort on pages IS NULL first, then pages.
SQL ServerFirstNo NULLS LAST: sort on CASE WHEN pages IS NULL THEN 1 ELSE 0 END first.
OracleLastNULL counts as larger than any value. NULLS FIRST / NULLS LAST change it.
Sybase ASEFirstNo NULLS LAST: sort on a CASE expression first, as in SQL Server.

Know someone fighting with a spreadsheet? Send them this page.