Sorting Data

By default, rows are stored in the database in an arbitrary order. Using SQL, you can apply a definite order to the set of rows returned by your query. For example, you can get a set of rows that's sorted in reverse alphabetical order. Obviously, a row in its entirety doesn't have an obvious value to sort by, so instead you need to specify a column for sorting.

The syntax is as follows:

SELECT ColumnA, ColumnB, ColumnC FROM Table
ORDER BY ColumnA;

Here, the ORDER BY clause is used to specify that ColumnA should be used to sort the data. The values in this column are examined according to their type, which might be alphabetic, numeric, timestamp, or whatever, and sorted accordingly.

The default behavior is to sort rows into an ascending order, so if the column to be sorted by is alphabetic, then rows will be returned sorted from A to Z according to the selected column. The previous query is shorthand for the following:

SELECT ColumnA, ColumnB, ColumnC FROM Table
ORDER BY ColumnA ASC;

Although this is the default behavior so you don't need to explicitly specify the ASC keyword, it's a good idea to include it to ensure that your queries are as easy to read as possible.

Alternatively, you may want to sort your rows in descending order, in which case you use the DESC keyword:

SELECT ColumnA, ColumnB, ColumnC FROM Table
ORDER BY ColumnA DESC;

For example, if you obtained a list of products from a Products table, you might want to order them according to their names:

SELECT ProductID, ProductName, AmountInStock FROM Products
ORDER BY ProductName ASC;

The following example shows a query on the InstantUniversity database, which orders the results alphabetically in reverse order:

SELECT Name, ProfessorID FROM Professor ORDER BY Name DESC;

Here are the results of the query:

   Name             ProfessorID

   ---------------- -----------
   Prof. Williams   2
   Prof. Patel      4
   Prof. Jones      5
   Prof. Hwa        6
   Prof. Dawson     1
   Prof. Ashby      3