When a data set is returned from a database query, the result may not produce an output in the sort order that we need. We can resolve this by adding the SQL ORDER BY clause to the end of our query. In fact, we can use SQL to order by multiple columns.
SELECT * FROM Customer ORDER BY LastName; — Order by single column
By adding the ORDER BY clause, we can see the list of last names appear in alphabetical order. We can even be more specific by adding additional columns for ordering. As we can see in the following sql example we can order by multiple columns. In fact, we can sort our data using SQL by as many columns as needed.
SELECT * FROM Customer ORDER BY LastName, FirstName; — SQL Order by 2 things
By default, the ORDER BY clause will sort the data in ascending order but for peace of mind we can add the ASC syntax for clarification.
SELECT * FROM Customer ORDER BY LastName ASC; — SQL Order by single column ascending
Or if we want to order descending we can alter the ending syntax.
SELECT * FROM Customer ORDER BY LastName DESC;
Now our results show last names starting from ‘Zimmermann’. We can even sort first name descending and then last name ascending. Just be aware that SQL will always sort by the column order specified meaning if we modify the column order in the ORDER BY clause, we would expect a different result.
SELECT * FROM Customer ORDER BY FirstName DESC, LastName ASC;
One last tip, we can use short-hand when writing an ORDER BY in SQL by declaring the column number in our select statement. For example:
SELECT FirstName, LastName FROM Customer ORDER BY 2;
Since the LastName field is our 2nd column in the SELECT statement, that is what our ORDER BY clause is actually using. Had we switched the first and last names in the SELECT, we would be ordering by FirstName instead. Additionally, we could change the ORDER BY clause to 1 if we wanted to sort by FirstName.
Again, we can see that if we use SQL to order by 2 things then it’s possible to use numeric values rather than column names. Either of these options work and are valid although using column names is easier to read.
If by some reason you accidentally sorted by 3, the database would return an error. This value is considered out of range since we’ve only selected 2 columns.
