Sorting with ORDER BY

Introduction

With ORDER BY you can sort tables ascending or descending.

• This can be done for different data types, such as numbers, text, dates.

• By default, sorting is ascending

  • From small to large.
  • From a to z.
  • From old to new.

• You can sort by 1 column, but also by multiple at the same time.

---

1. Sorting text

In the example below, we use ORDER BY:

• To sort ascending by the name of a movie.

SELECT
  *
FROM
  movies
ORDER BY
  name;

id	name	year	rank
10920	Aliens	1986	8.2
17173	Animal House	1978	7.5
18979	Apollo 13	1995	7.5
...	...	...	...

Columns: 4
Rows: 36

• You can see that it is sorted ascending, from a to z.

---

2. Sorting numbers

In the example below, we use ORDER BY:

• To sort ascending by the year of a movie.

SELECT
  *
FROM
  movies
ORDER BY
  year;

id	name	year	rank
130128	Godfather, The	1972	9
313459	Star Wars	1977	8.8
17173	Animal House	1978	7.5
...	...	...	...

Columns: 4
Rows: 36

• You can see that it is sorted ascending, from small to large.

---

3. Sorting ascending or descending

We have seen that by default sorting is ascending.

• This is not always desirable.
• Sometimes you want to sort descending.
• For this, you can use the keyword DESC.
• Descending stands for descending: DESC.
• Ascending stands for ascending: ASC.
  • This is the default.

---

3.1. Sorting ascending with keyword ASC

In the example below, we use ORDER BY and ASC:

• To sort ascending by the year of a movie.

SELECT
  *
FROM
  movies
ORDER BY
  year ASC;

id	name	year	rank
130128	Godfather, The	1972	9
313459	Star Wars	1977	8.8
17173	Animal House	1978	7.5
...	...	...	...

Columns: 4
Rows: 36

• You can see that it is sorted ascending, from small to large.
• This is the default, we would have gotten the same result without the keyword ASC.

---

3.2. Sorting descending with keyword DESC

In the example below, we use ORDER BY and DESC:

• To sort descending by the year of a movie.

SELECT
  *
FROM
  movies
ORDER BY
  year DESC;

id	name	year	rank
30959	Batman Begins	2005	(NULL)
124110	Garden State	2004	8.3
176712	Kill Bill: Vol. 2	2004	8.2
...	...	...	...

Columns: 4
Rows: 36

• You can see that it is sorted descending, from large to small.

---

4. Sorting on multiple columns

We now know how to sort ascending or descending on one column.

• You can also sort on multiple columns.
• You can sort both ascending and descending.
• You use a comma , between the column names.

---

4.1. Default (ascending) sorting on multiple columns

In the example below, we use ORDER BY and sort on multiple columns:

• We first sort by the movie's rating: column rank.
• Then we sort by the year of the movie: column year.

SELECT
  *
FROM
  movies
ORDER BY
  rank,
  year;

id	name	year	rank
147603	Hollow Man	2000	5.3
116907	Footloose	1984	5.8
344203	UHF	1989	6.6
...	...	...	...
17173	Animal House	1978	7.5
111813	Few Good Men, A	1992	7.5
18979	Apollo 13	1995	7.5
...	...	...	...

Columns: 4
Rows: 36

• You can see that it is first sorted ascending by rank.
• And then ascending by year.

---

4.2. Specific sorting on multiple columns

In the example below, we use ORDER BY and sort on multiple columns, with keywords ASC and DESC:

• We first sort ascending (ASC) by the movie's rating: column rank.
• Then we sort descending (DESC) by the year of the movie: column year.

SELECT
  *
FROM
  movies
ORDER BY
  rank ASC,
  year DESC;

id	name	year	rank
147603	Hollow Man	2000	5.3
116907	Footloose	1984	5.8
344203	UHF	1989	6.6
...	...	...	...
238072	Ocean”s Eleven	2001	7.5
254943	Pi	1998	7.5
18979	Apollo 13	1995	7.5
...	...	...	...

Columns: 4
Rows: 36

• You can see that it is first sorted ascending by rank.
• And then descending by year.

---

Summary

• With ORDER BY you can sort by columns.
  • By default, this is ascending (from small to large).
  • For example:

SELECT
  *
FROM
  movies
ORDER BY
  name;

• With keywords, you can sort ascending or descending.
  • Descending stands for descending: DESC.
  • Ascending stands for ascending: ASC.
  • For example:

SELECT
  *
FROM
  movies
ORDER BY
  year ASC;

• You can sort on multiple columns.
  • You use a comma , between the column names.
  • For example:

SELECT
  *
FROM
  movies
ORDER BY
  rank ASC,
  year DESC;