Order (Sort) Data with ORDER BY

Now you know how to get all of the columns and rows with SELECT *.

You can select only the columns you want with SELECT col1, col2, ... colN ....

And you also know how to get only the rows you want by filtering them using WHERE.

Now let's see how to sort the results.

Select with Order By

It is relatively simple to sort data any way that you want to. You simply specify ORDER BY as a clause in the SELECT statement and then specify the columns that you want to sort on as part of the ORDER BY clause.

The data is already sorted in ascending order in the new_roots table (by root), because this is the order the data was entered in the roots table. Ascending order is specified with a special keyword, ASC when you specify sorting a column. Ascending order is going from the first character alphabetically until the last character, that is, in English, sorting from A->Z. How about we want to sort from Z-A? We can specify descending order, which, in English, is Z->A. We add the keyword DESC after the column that we want in descending order.

As a reminder:

• Character Encoding is the set of characters you can type. In DB Browser, the default character set is UTF-8. This contains a lot of characters, probably all of the ones your language might need, but maybe not all of them.
• Collation Sequence is the sort order applied to a character set. Each column gets an assigned character set (UTF-8) and a default sort order (collation sequence) when that column contains TEXT. The English rules for sorting characters are applied by default. These rules might not match your language of interest. The character sets and collation sequences can be changed from their defaults, but we are not covering that in this tutorial.

Here is the unsorted data from the new_roots table:

SELECT *
FROM new_roots;

If you try that in DB Browser for SQLite, you will get this result:

DB Browser screenshot showing the result of running a SQL SELECT statement against the new_roots table without specifying a sort order.

SQL with ORDER BY

Here we are introduced to the new SQL clause ORDER BY. You use the ORDER BY clause to specify the way you want the results sorted. The general syntax is:

SELECT *
FROM new_roots
ORDER BY col1 [ASC/DESC], col2 [ASC/DESC], ... colN [ASC/DESC];

Sort by col1, either ascending (by default), or descending. Then sort by col2, then col3, ... until the last column.

Here is an example. Let's sort by root, descending.

SELECT id, root, nicodemus, english
FROM new_roots
ORDER BY root DESC;

If you try that in DB Browser for SQLite, you will get this result:

DB Browser screenshot showing the result of running a SQL SELECT statement against the new_roots table with a descending sort order.

Okay, but now we want to also make sure that within the roots, we also sort by nicodemus in descending order so that all of the data is in descending order. In order to do that, you can type:

SELECT id, root, nicodemus, english
FROM new_roots
ORDER BY root DESC, nicodemus DESC;

If you try that in DB Browser for SQLite, you will get this result:

DB Browser screenshot showing the result of running a SQL SELECT statement against the new_roots table with a descending sort order.

Recap

In conjunction with how you filter the data with WHERE you can now change the sorting of the results with ORDER BY.

What's Next

What if you want the table returned by your SQL command to have a specific number of rows, starting from the first record, or even in specific-sized chunks? You can use LIMIT and OFFSET statements! 🚀