8. Sorting records

“Well, it looks like a mess, is there any way to give this data some order?” – asked Gessica after they ran their first query and looked at the records.

User records indeed looked weird – there is a column named id but ids didn’t follow a particular order. Same with dates. Why it is so?

The thing is that the database decides how the data should be stored. When you query the database it tries to deliver results asap, so if you didn’t ask to sort the records it won’t do it.

We’re already familiar with SELECT and FROM keywords. Let’s add another one to the mix: ORDER BY. Here’s the example query:

SELECT *
FROM users
ORDER BY signup_date DESC

This query prints out all records of the users table ordered by values of signup_date column in the descending order (highest first, like Z..A in Google Spreadsheets). Alternatively, we can retrieve records in the ascending order (lowest first, A..Z):

SELECT *
FROM users
ORDER BY first_name ASC

Multiple orders in one query

The same way we can select multiple columns in the SELECT statement, we can also specify multiple columns in the ORDER statement. For example:

SELECT *
FROM users
ORDER BY country ASC, signup_date DESC

This query will select all records from the users table and sort them first by the country in the ascending order (au, …, de, …, us, …): we’ll have a lot of records per country and they’ll be grouped together.

Then SQL will sort records by values of the signup_date column ⚠ without breaking the order by country. It means that within each country group records will be sorted by signup_date column in the descending order.

It’s a perfect moment to go to the Playground and practice sorting results. Try to sort the users or the books table by different columns. 💡

Anatoli Makarevich, author of SQL Habit About SQL Habit

Hi, it’s Anatoli, the author of SQL Habit. 👋

SQL Habit is a course (or, as some of the students say, “business simulator”). It’s based on a story of a fictional startup called Bindle. You’ll play a role of their Data Analyst 📊 and solve real-life challenges from Business, Marketing, and Product Management.

SQL Habit course is made of bite-sized lessons (you’re looking at one atm) and exercises. They always have a real-life setting and detailed explanations. You can immediately apply everything you’ve learned at work. 🚀

“well worth the money”

Fluent in SQL in a month

Master Data Analysis with SQL with real life examples from Product Management, Marketing, Finance and more.
-- Type your query here, for example this one -- lists all records from users table: SELECT * FROM users
LIMIT 500
Loading chart... ⏳