Sort Results Part 2

Part of the Fundamentals section of Coddy's SQL journey — lesson 14 of 72.

When using ORDER BY in SQL, you can sort data by multiple columns.

For example consider the following competition table:

runner_id	age	avg_speed (m/s)
1	47	3.65
2	62	3.07
3	57	6.82
4	56	4.34
5	25	4.93
6	40	3.94
7	23	6.58
8	40	3.43

SELECT *
FROM competition
WHERE age < 50
ORDER BY age DESC, avg_speed DESC

This query will:

First sort all records by age in descending order (highest to lowest)
If two or more records have the same age, it will then sort those specific records by avg_speed in descending order (highest to lowest)

The result:

runner_id	age	avg_speed (m/s)
1	47	3.65
6	40	3.94
8	40	3.43
5	25	4.93
7	23	6.58

Challenge

Easy

Available tables and columns:
feathers: id, weight, area

Return all of the ids after ordering them by the area in descending order. If the feathers have the same area, sort them by weight in ascending order.

Cheat sheet

You can sort data by multiple columns using ORDER BY. The first column specified takes priority, and subsequent columns are used as tie-breakers:

SELECT *
FROM competition
ORDER BY age DESC, avg_speed DESC

This will:

First sort by age in descending order
If records have the same age, then sort by avg_speed in descending order

Try it yourself

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in Fundamentals

3Specific Return Format

Null values Sort Results Part 1Sort Results Part 2Recap - Cyber Security Firm Limit number of records Recap - Vehicle Factory

6Intro Challenges

Recap - Parliamentary Election Recap - Police Criminal Arrest Recap - Bar Beverage Container Recap - Engineer new columns

9Multiple tables

Basic Join Part 1 Basic Join Part 2 Recap - Join Self join Recap - Self Join Union Simplify queries, WITH keyword Recap - With Queries Recap - Real Estate Contractor

12Window Functions part 2

RANK & DENSE_RANK Functions Recap - RANK & DENSE_RANK NTILE Function Aggregation Functions ROWS & RANGE Criterion