SQL ORDER BY

SQL ORDER BY clause is used to order the data sets retrieved from a SQL database. The ordering of the selected data can be done by one or more columns in a table. If we want to sort our Users table by the FirstName column, we'll have to use the following ORDER BY SQL statement:

SELECT * FROM Users ORDER BY FirstName

The result of the ORDER BY statement above will be the following:

FirstNameLastNameDateOfBirthEmailCity
DavidStonewall01/03/1954david@sql-tutorial.comSan Francisco
JohnSmith12/12/1969john.smith@john-here.comNew York
PaulO'Neil09/17/1982paul.oneil@pauls-email.comNew York
StephenGrant03/03/1974sgrant@sgrantemail.comLos Angeles
SusanGrant03/03/1970susan.grant@sql-tutorial.comLos Angeles

As you can see the rows are ordered alphabetically by the FirstName column.

You can use ORDER BY to order the retrieved data by more than one column. For example, if you want to order by both LastName and City columns, you would do it with the following ORDER BY statement:

SELECT * FROM Users ORDER BY LastName, DateOfBirth

Here is the result of this ORDER BY statement:

FirstNameLastNameDateOfBirthEmailCity
SusanGrant03/03/1970susan.grant@sql-tutorial.comLos Angeles
StephenGrant03/03/1974sgrant@sgrantemail.comLos Angeles
PaulO'Neil09/17/1982paul.oneil@pauls-email.comNew York
JohnSmith12/12/1969john.smith@john-here.comNew York
DavidStonewall01/03/1954david@sql-tutorial.comSan Francisco

When using ORDER BY with more than one column, you need to separate the list of columns following ORDER BY with commas.

What will happen if we reverse the order of the columns specified after the ORDER BY statement like in the statement below?

SELECT * FROM Users ORDER BY DateOfBirth, LastName

This ORDER BY statement will return the same results as the one with the reversed columns order, but they will be ordered differently. Here is the result:

FirstNameLastNameDateOfBirthEmailCity
DavidStonewall01/03/1954david@sql-tutorial.comSan Francisco
JohnSmith12/12/1969john.smith@john-here.comNew York
SusanGrant03/03/1970susan.grant@sql-tutorial.comLos Angeles
StephenGrant03/03/1974sgrant@sgrantemail.comLos Angeles
PaulO'Neil09/17/1982paul.oneil@pauls-email.comNew York

The ORDER BY clause first sorts the retrieved data by the first column, then the next one, and so forth.

In all the ORDER BY examples so far, we were sorting alphabetically for character columns (FirstName, LastName) and from earlier to later date for the DateOfBirth column. What do we do if we want to order our data alphabetically but this time backwards? In order to accomplish that we need to use the DESC SQL keyword:

SELECT * FROM Users ORDER BY FirstName DESC

Here is the result:

FirstNameLastNameDateOfBirthEmailCity
SusanGrant03/03/1970susan.grant@sql-tutorial.comLos Angeles
StephenGrant03/03/1974sgrant@sgrantemail.comLos Angeles
PaulO'Neil09/17/1982paul.oneil@pauls-email.comNew York
JohnSmith12/12/1969john.smith@john-here.comNew York
DavidStonewall01/03/1954david@sql-tutorial.comSan Francisco

When you add the keyword DESC after a column name in the ORDER BY clause, you are still ordering by this column but the result is retrieved backwards. The opposite of the DESC keyword is the ASC keyword which orders by the specified columns alphabetically. But how did our previous statements know to order the data alphabetically, when we didn't specify the ASC keyword? The answer is simple, when you don't specify ASC or DESC after a column in the ORDER BY column list, then the ordering is done ASC (alphabetically, from low to high) by default.

It's important to remember that whenever you are ordering by more than one column, you need to specify ASC and/or DESC after each column, if you need specific ordering. For example the statement below will order by both LastName and DateOfBirth but only LastName will be in descending order:

SELECT * FROM Users ORDER BY DateOfBirth, LastName DESC

If you want to order descending by both columns you need to change the ORDER BY statement to this:

SELECT * FROM Users ORDER BY DateOfBirth DESC, LastName DESC.

High traffic sites that have lots of queires to their database should look for web hosts with good reviews that provide the best managed hosting.