32  SQL Select Order by

If order by is not used in the query, database engine is free to return result set in whichever order it chooses.

SELECT * FROM Customer c 
order by FirstName
SELECT * FROM Customer c 
order by Country , State, City
SELECT CustomerId,FirstName,LastName FROM Customer c 
order by Country , State, City
SELECT CustomerId,FirstName,LastName FROM Customer c 
order by CustomerId DESC
SELECT CustomerId,FirstName,LastName FROM Customer c 
order by Country DESC, State DESC, City DESC
SELECT CustomerId,FirstName,LastName FROM Customer c 
order by 1 DESC

Order by should be used in the grids and other lists since most users expect the result set to be ordered. For example, a simple user interface usage of order by of cities. In an address selection drop box down list, we would like to see most important cities first.

In the below examples, we see cities ordered by names only.

SELECT CityId, CityName from Cities
ORDER BY  CityName

This usage is not very good since most of the users will come from İstanbul and Ankara. Lets change the query so that these two cities will come first in the result set. We need to add column OrderPriority to cities table. And add values to İstanbul and Ankara for this column. Since OrderPriority values in İstanbul and Ankara is higher than other cities, we would see city list as below:

SELECT CityId, CityName, OrderPriority from Cities
ORDER BY OrderPriority DESC, CityName ASC