Database Access with Visual Basic .NET (3rd Edition)

Sorting Results with ORDER BY

The ORDER BY clause tells the database engine to sort the records it retrieves. You can sort on any field, or on multiple fields, and you can sort in ascending or descending order. To specify a sort order, include the ORDER BY clause at the end of any SELECT query, followed by the name of the field or fields by which you want to sort. For example, to return a list of customer's names sorted by last name, use

SELECT ID, FirstName, LastName FROM tblCustomer ORDER BY LastName

This query retrieves all customers from the database, arranging them by last name.

Sorting in Descending Order

To sort in descending order, use the DESC keyword after the field by which you're sorting. For example, to retrieve records from the tblOrder table according to who placed the most recent order, use

SELECT * FROM tblOrder ORDER BY OrderDate DESC

This query retrieves all orders from tblOrder, arranged with the newest order first.

Sorting on Multiple Fields

To sort on multiple fields, list the fields one after the other immediately following the ORDER BY clause, delimited by commas. For example, to sort tblCustomer by last name, then by first name, use the SQL query

SELECT FirstName, LastName, City, State FROM tblCustomer ORDER BY LastName, FirstName

This query retrieves all customers from the database. Unlike our earlier customer query, the two customers whose last names are identical (Betty Klein and Daisy Klein) are sorted correctly this time.