SQL Order By Query Examples
SQL Order By clause is used to sort data returned by the execution of SQL queries developed by SQL programmers or database administrators. By default SQL engine returns query result without sorting and ordering data rows. So if you require to order data or sort data returned by SQL query execution, you have to define the order criterias by using SQL Order By clause.
Let's create sample SQL database table names Contacts which will hold sample SQL data for our ORDER BY tutorial. Sample table has three fields: Id, Firstname and Lastname. We will create SQL SELECT statements with ORDER BY according to firstname and lastname fields to sort data.
Create Table Contacts (
Id smallint identity(1,1),
FirstName nvarchar(100),
LastName nvarchar(100)
)
Now, let's populate SQL table Contacts with sample data. Please note that there are three SQL books authors here with same firstname Peter in purpose.
Insert Into Contacts (FirstName, LastName) Values (N'Itzik', N'Ben-Gan')
Insert Into Contacts (FirstName, LastName) Values (N'Dejan', N'Sarka')
Insert Into Contacts (FirstName, LastName) Values (N'Ron', 'Talmage')
Insert Into Contacts (FirstName, LastName) Values (N'Peter', 'Rob')
Insert Into Contacts (FirstName, LastName) Values (N'Ben', 'Forta')
Insert Into Contacts (FirstName, LastName) Values (N'Zaitsev', 'Kendall')
Insert Into Contacts (FirstName, LastName) Values (N'Lynn', 'Beighley')
Insert Into Contacts (FirstName, LastName) Values (N'Zaitsev', 'Zaitsev')
Sort Query Result using SQL Order By Clause
In order to return the contacts list from SQL Server database table, SQL SELECT statement is used. Without using ORDER BY clause, returned rows are not sorted by any column.
SELECT Id, FirstName, LastName FROM Contacts
Return data set seems to be returned in the insert order but this is not guaranteed. SQL Engine returns the data from Contacts table data pages. And data pages might be containing data not sorted by any value. To summarize, without an SQL ORDER BY clause, returned dataset is not ordered by any criteria.
If you want to display your contacts on your web application ordered by their first names using indexed as their first letters, you may want to list contact persons in order by their firstnames. In your T-SQL code, if you use ORDER BY clause and following it the sort column it is possible to return the contacts list ordered for a specific column like first name.
SELECT Id, FirstName, LastName FROM Contacts ORDER BY FirstName
ORDER BY clause has one option right after each column value which has the default ASC Ascending or DESC Descending value.
ASC or Ascending defines the order will be starting from small values through larger or bigger values.
And DESC or Descending sorts the data set from the last value through the first value which is the opposite order of ASC Ascending behaviour.
In the above SQL query example, we omit ASC since it is already the default behaviour of the SQL Engine.
SELECT Id, FirstName, LastName FROM Contacts ORDER BY FirstName ASC
SELECT Id, FirstName, LastName FROM Contacts ORDER BY FirstName DESC
If you want ascending order in SQL query outputs, you can use ASC key or leave it blank since ASC ascending order is default in SQL Order By clause. But if you require descending order in SQL query results, then you must use explicitly the DESC descending key right after the column name in Order By clause.
SQL Order By in Multiple Columns
Of course using a single column after ORDER BY clause in SQL SELECT statement may not be sufficient. In the above SQL query examples, you will see three contacts with firstname Peter. And if you look carefully on the last return list, you will see that the returned rows are not ordered according to any criteria.
Now assume that for the sake of our web application where contacts are listed on web pages, we need our SQL developer to return the contact list dataset in order according to firstname and lastname. We have already dataset sorted by firstname, but now we need additional column to sort data with same first name values. And this second column criteria for Order By clause is lastname field. Let's now build the Select statement in SQL Order By multiple columns version instead of single column Order By syntax.
SELECT Id, FirstName, LastName FROM Contacts ORDER BY FirstName, LastName
Return SQL Select list in order according to multiple columns using SQL ORDER BY multiple columns
Please note that in above sql query example, the dataset returned is sorted by both firstname and lastname columns in ascending order. Since ASC ascending order in SQL ORDER BY statement is default, it is not needed to explicetly write the ASC key. Now assume that we want to list contact list starting from last contact to the first contact in alphabetical order. The solution of this SQL problem is to use the DESC descending order in SQL Select statement.
Here is the sql query which sorts data rows in reverse order, starting from last to the first.
SELECT * FROM Contacts ORDER BY FirstName DESC, LastName DESC
SQL programmers will realize that ASC or DESC hint is applied to the column name before it is used. So developers can use ASC for one column and DESC for an other without affecting each other as in following T-SQL query example
SELECT * FROM Contacts ORDER BY FirstName ASC, LastName DESC
As one last word about SQL Order By, one of last T-SQL programming enhancements was related with Transact-SQL Order By clause. With SQL Server 2012, sql developers can make paging using SQL Order By with new added OFFSET and FETCH NEXT commands. You can refer to given reference SQL tutorial for additions to Order By clause in SQL Server.