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)

I

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!