Sort Results Using the ORDER BY clause in SQL

The ORDER BY clause sorts query results by one or more columns. By default the rows in a result set returned from a SELECT query are not in any particular order. To have your result set in a particular order, you will need to sort the results using the order by clause.

SQL ORDER BY Examples

.

Specifying a column that is not defined in the select list

The following example orders the result set by a column that is not included in the select list, but is defined in the table specified in the FROM clause.

.

Note: The query results are listed in ascending order of ListPrice. By default, the ORDER BY clause applies an ascending sort order to the specified field. To sort the results into descending order of ListPrice, i.e. ORDER BY ListPrice DESC

Ascending and Descending Sort Order

You have the option of sorting in ascending (ASC) or descending (DESC). If neither is specified, records are sorted in ascending order (ASC) by default. To sort records in descending order, you will need to add the DESC keyword.

SELECT column, column
FROM table
ORDER BY column DESC, column ASC;

Specifying a descending order

For example, the following query orders the result set by the ListPrice column in descending order, sorted by the most expensive items first.

.

Specifying an ascending order

The following query example orders the result set by the Name column in ascending order. The characters are sorted alphabetically, not numerically. That is, 10 sorts before 2.

.

Specifying both ascending and descending order

This query sorts the results into descending order of ListPrice, and then into ascending order of Name.

.

The following example orders the result set by two columns. The query result set is first sorted in ascending order by the FirstName column and then sorted in descending order by the LastName column.

.

-- WHERE CLAUSE -- Simple filter SELECT AddressLine1, City, PostalCode FROM SalesLT.Address WHERE CountryRegion = 'United Kingdom' ORDER BY City, PostalCode; -- Multiple criteria (and) SELECT AddressLine1, City, PostalCode FROM SalesLT.Address WHERE CountryRegion = 'United Kingdom' AND City = 'London' ORDER BY PostalCode; -- Multiple criteria (or) SELECT AddressLine1, City, PostalCode, CountryRegion FROM SalesLT.Address WHERE CountryRegion = 'United Kingdom' OR CountryRegion = 'Canada' ORDER BY CountryRegion, PostalCode; -- Nested conditions SELECT AddressLine1, City, PostalCode FROM SalesLT.Address WHERE CountryRegion = 'United Kingdom' AND (City = 'London' OR City = 'Oxford') ORDER BY City, PostalCode; -- Not equal to SELECT AddressLine1, City, PostalCode FROM SalesLT.Address WHERE CountryRegion = 'United Kingdom' AND City <> 'London' ORDER BY City, PostalCode; -- Greater than SELECT AddressLine1, City, PostalCode FROM SalesLT.Address WHERE CountryRegion = 'United Kingdom' AND City = 'London' AND PostalCode > 'S' ORDER BY PostalCode; -- Like with wildcard SELECT AddressLine1, City, PostalCode FROM SalesLT.Address WHERE CountryRegion = 'United Kingdom' AND City = 'London' AND PostalCode LIKE 'SW%' ORDER BY PostalCode; -- Like with regex pattern SELECT AddressLine1, City, PostalCode FROM SalesLT.Address WHERE CountryRegion = 'United Kingdom' AND City = 'London' AND PostalCode LIKE 'SW[0-9] [0-9]__' ORDER BY PostalCode; -- check for null SELECT AddressLine1, AddressLine2, City, PostalCode FROM SalesLT.Address WHERE AddressLine2 IS NOT NULL ORDER BY City, PostalCode; -- within a range SELECT AddressLine1, ModifiedDate FROM SalesLT.Address WHERE ModifiedDate BETWEEN '01/01/2005' AND '12/31/2005' ORDER BY ModifiedDate; -- In a list SELECT AddressLine1, City, CountryRegion FROM SalesLT.Address WHERE CountryRegion IN ('Canada', 'United States') ORDER BY City;

.

/////////////Exercises /////////////////////// Sorting Results In this exercise, you will practice sorting results in SELECT statements. 1. Select CategoryName and Description from the Categories table sorted by CategoryName. 2. Select ContactName, CompanyName, ContactTitle, and Phone from the Customers table sorted by Phone. 3. Create a report showing employees’ first and last names and hire dates sorted from newest to oldest employee. Note the way your database displays dates. This display is quite often different from database to database. 4. Create a report showing Northwind’s orders sorted by Freight from most expensive to cheapest. Show OrderID, OrderDate, ShippedDate, CustomerID, and Freight. 5. Select CompanyName, Fax, Phone, HomePage and Country from the Suppliers table sorted by Country in descending order and then by CompanyName in ascending order. 6. Create a list of employees showing title, first name, and last name. Sort by Title in ascending order and then by LastName in descending order. //////////////////// Solution: SELECT CategoryName, Description FROM Categories ORDER BY CategoryName; SELECT ContactName, CompanyName, ContactTitle, Phone FROM Customers ORDER BY Phone; SELECT FirstName, LastName, HireDate FROM Employees ORDER BY HireDate DESC; SELECT OrderID, OrderDate, ShippedDate, CustomerID, Freight FROM Orders ORDER BY Freight DESC; SELECT CompanyName, Fax, Phone, HomePage, Country FROM Suppliers ORDER BY Country DESC, CompanyName; SELECT Title, FirstName, LastName FROM Employees ORDER BY Title ASC, LastName DESC; ///////////////////////////////////

.

.

///////////////////////Copy Code ////////////////// ///////////////////////////// ///////////////////////Copy Code ////////////////// /////////////////////////////

How to Sort Rows with ORDER BY

#Emeka - pubs-script-1.jpg

This query returns results ordered by ascending pub_id:
Here is the result set:

How to Name more than one column in the ORDER BY clause

If more than one column is named in the ORDER BY clause, sorts are nested. The following statement sorts the rows in the titles table, first by publisher in descending order, and then by type in ascending order within each publisher, and finally by price (also ascending, because DESC is not specified).

Here is the result set:

SQL ORDER BY - Do's & Don'ts

//////////////////////////

Related Content