As we present the results of a query to those who will use the data, we will often want to sort the information to help us make sense of it. We can normally sort by any of the columns in our table. There are two ways to sort data: Ascending (A-Z, 1-10) and Descending (Z-A, 10-1). We use another keyword in SQL to indicate this: ORDER BY. By default, all of our statements will use the Ascending order keyword ASC to sort the data. If we want the data sorted Descending, we need to use the keyword DESC.
Using the example of all the cats that start with GAR, using the keyword LIKE, and sorting by the name of the cat, our query would look like this:
[sql]SELECT NameOfCat, BreedOfCat, ColorOfCat
FROM Cats
WHERE NameOfCat LIKE ‘Gar%’
ORDER BY NameOfCat;[/sql]
OUTPUT
NameOfCat BreedOfCat ColorOfCat
———- —————— ————–
Gareth Snowshoe White
Gareth American Shorthair Grey
Garfield Tabby Orange
Garret Brazilian Shorthair Brown
Garret Turkish Angora Grey
Garth Russian Tabby Grey
Garth Somali Brown
Gary Sokoke Grey
Gary American Curl Grey
(9 row(s) affected)
This puts the results of the query in alphabetical order by the name of the cat so it will be much easier to find the cat we are looking for. In the query below, I added the ASC keyword to the query; however, to the database it is the same as the query above.
[sql]SELECT NameOfCat, BreedOfCat, ColorOfCat
FROM Cats
WHERE NameOfCat LIKE ‘Gar%’
ORDER BY NameOfCat ASC;[/sql]
OUTPUT
NameOfCat BreedOfCat ColorOfCat
—————- ——————— ————–
Gareth Snowshoe White
Gareth American Shorthair Grey
Garfield Tabby Orange
Garret Brazilian Shorthair Brown
Garret Turkish Angora Grey
Garth Russian Tabby Grey
Garth Somali Brown
Gary Sokoke Grey
Gary American Curl Grey
(9 row(s) affected)
If we wanted the cats listed in reverse alphabetical order, our query would look like this:
[sql]SELECT NameOfCat, BreedOfCat, ColorOfCat
FROM Cats
WHERE NameOfCat LIKE ‘Gar%’
ORDER BY NameOfCat DESC;[/sql]
OUTPUT
NameOfCat BreedOfCat ColorOfCat
—————- ——————— —————-
Gary Sokoke Grey
Gary American Curl Grey
Garth Russian Tabby Grey
Garth Somali Brown
Garret Turkish Angora Grey
Garret Brazilian Shorthair Brown
Garfield Tabby Orange
Gareth Snowshoe White
Gareth American Shorthair Grey
(9 row(s) affected)
In the above query, we find we have three cats with the name Garfield. We also see the breed order is not in alphabetical order. SQL gives us the option to sort or ORDER BY more than one column. This is useful when the first column has duplicate data and we need a second criteria to use for sorting. Again, ASC is assumed by the database.
[sql]SELECT NameOfCat, BreedOfCat, ColorOfCat
FROM Cats
WHERE NameOfCat LIKE ‘Gar%’
ORDER BY NameOfCat, BreedOfCat;[/sql]
OUTPUT
NameOfCat BreedOfCat ColorOfCat
————— ———————- ————-
Gareth American Shorthair Grey
Gareth Snowshoe White
Garfield Tabby Orange
Garret Brazilian Shorthair Brown
Garret Turkish Angora Grey
Garth Russian Tabby Grey
Garth Somali Brown
Gary American Curl Grey
Gary Sokoke Grey
(9 row(s) affected)
We can also sort column in different directions. If we wanted to sort our list by the length of time a cat has been with their owner, and then by the name of the cat, our query would look like this:
[sql]SELECT NameOfCat, BreedOfCat, MonthsWithOwner
FROM Cats
WHERE NameOfCat LIKE ‘Gar%’
ORDER BY MonthsWithOwner DESC, NameOfCat ASC;[/sql]
OUTPUT
NameOfCat BreedOfCat MonthsWithOwner
————– ——————— —————
Garfield Tabby 444
Garth Somali 165
Gary Sokoke 162
Gareth Snowshoe 161
Gary American Curl 87
Gareth American Shorthair 70
Garth Russian Tabby 65
Garret Turkish Angora 49
Garret Brazilian Shorthair 5
(9 row(s) affected)
The cats with longer time with their owners will be displayed first, and each cat with the same MonthsWithOwner will be listed in alphabetical order. What’s more, we are sorting by a column we have not asked for in the result set. What will be returned is NameOfCat, BreedOfCat, and ColorOfCat. MonthsWithOwner will not be displayed in the result. We can use ANY of the columns in our table to manipulate our results.
We don’t, in fact, even have to order by the name of the column, we can simply state the column number. Be careful with this one—your code will undoubtedly change, and you could inadvertently change the order of your result set because you failed to update the ORDER BY columns. For this reason, I strongly recommend against this approach.
[sql]Select NameOfCat, BreedOfCat, MonthsWithOwner
FROM Cats
WHERE NameOfCat LIKE ‘Gar%’
ORDER BY 1, 2;[/sql]
OUTPUT
NameOfCat BreedOfCat MonthsWithOwner
—————- ——————– —————
Gareth American Shorthair 70
Gareth Snowshoe 161
Garfield Tabby 444
Garret Brazilian Shorthair 5
Garret Turkish Angora 49
Garth Russian Tabby 65
Garth Somali 165
Gary American Curl 87
Gary Sokoke 162
(9 row(s) affected)
Want to follow along on your own database?
Do you want to follow along with the examples? We have created a sample database you can use to follow along and try in your own database. Go to the download page and follow the instructions. Have fun on the SQL trail!