The Takeaways

By the end of this tutorial, make sure you can answer these questions:

  • How to sort results by a column in ascending and descending order.

  • How to sort by multiple columns.

  • How to show only the top 5 rows of an ordered result set.

Get the data

For this lesson, download the following file, which is a SQLite database built from the U.S. Social Security Administration file of popular baby names:

Unzip it, and open the sqlite file using the SQLite client of your choice (e.g. DB Browser for SQLite)

Table of contents

The LIMIT clause

With SELECT, we were able to specify exactly how many columns we wanted in the returned dataset. With LIMIT, we can restrict the number of rows returned:

FROM baby_names


state sex year name count
AK F 2010 Sophia 60

Pretty easy, there's not much more to LIMIT than the keyword itself, followed by the number of rows we want to see.

Why use LIMIT when, well, we could just have all the data? Remember that more data is not always better. But the main concern is that in the real-world, retrieving data rows takes computational time. LIMIT is a good way to reduce the execution time. Even before you get to exporting data, returning 1,000,000 rows will just be slower than returning 10 rows, all other things being equal.

Mind the ordering of the syntax

At this point, we've only covered three different clauses. So the key thing to notice is the specific order and arrangement of the SQL statement: just as FROM comes after the SELECT clause, LIMIT comes after both.

Thus, the following queries will not work:

FROM baby_names;
FROM baby_names;

The ORDER BY clause

The ORDER BY clause, as you can imagine, let's us specify the sorting order of the returned data rows.

The basic syntax is:

ORDER BY "some_column_name"

Here's a standalone example:

FROM baby_names
ORDER BY count;

The truncated results:

state sex year name count
AK F 2010 Alayna 5
AK F 2010 Alicia 5
AK F 2010 Aliyah 5
AK F 2010 Amber 5
AK F 2010 Andrea 5

Sorting in descending order

By default, ORDER BY sorts in ascending order. When it comes to numbers, that means smallest first.

If we want to find the rows with the largest count values, we use the DESC keyword. The syntax looks like this:

ORDER BY "some_column_name" DESC

If we want to explicitly specify ascending order, we use the ASC keyword:

ORDER BY "some_column_name" ASC

To sort the baby names table in descending order of count:

FROM baby_names

The truncated results:

state sex year name count
USA F 2010 Isabella 22883
USA F 2012 Sophia 22267
USA M 2010 Jacob 22082
USA F 2011 Sophia 21816
USA F 2013 Sophia 21147

Sorting by multiple columns

In the case when the column to sort by has duplicate values, i.e. a tie, we can specify more columns to use in the sorting criteria. Just add more column names and ordering keywords – i.e. ASC and DESC – separated by commas. The ORDER BY keywords are only used once.

The following (somewhat nonsensical) query will return the rows in reverse-alphabetical order of state, then in ascending order of count, i.e. the least common names:

FROM baby_names
ORDER BY state DESC, count ASC;

The truncated results:

state sex year name count
WY F 2010 Ali 5
WY F 2010 Alice 5
WY F 2010 Allie 5
WY F 2010 Ariana 5
WY F 2010 Arianna 5
WY F 2010 Aubree 5

To modify the order so that the most recent year and the most common names are on top:

FROM baby_names
ORDER BY state DESC, year DESC, count DESC;
state sex year name count
WY F 2014 Olivia 40
WY M 2014 Jackson 34
WY F 2014 Brooklyn 32
WY F 2014 Emma 32
WY M 2014 Mason 30

How ORDER BY and LIMIT go together

Being able to order the result rows is particularly useful when using LIMIT, as it allows us to quickly return just the "top 3" or "bottom 10" of the results.

The ORDER BY clause goes after the FROM clause but before the LIMIT.

This example query returns name in reverse-alphabetical order and shows only the top 3 results:

FROM baby_names


The LIMIT clause | SQLite documentation
The ORDER BY clause | SQLite documentation