Ordering Data


You learnt how to update, delete, insert and retrieve data from SQL database and now itís time to learn how to order the data retrieved from a table. We will use the Countries table again, selecting all countries and ordering them by population. Consider the SELECT statement below:

SELECT Country, Continent, Capital, Population
FROM Countries
ORDER BY Population

The first 2 lines of the statement are already familiar, but the 3rd line is new. The ORDER BY clause specifies which column to order the results by. In our case this is the Population column. Here is the result of the ordering:


Country Continent Capital Population
Canada North America Ottawa 33,098,932
Italy Europe Rome 58,133,509
UK Europe London 60,609,153
Mexico North America Mexico 107,449,525
USA North America Washington 298,444,215


You can see that the result set has been ordered by the Population column in ascending order. Is it possible to order by the Population column in descending order? Yes, itís possible, and you can do it with the following statement:

SELECT Country, Continent, Capital, Population
FROM Countries
ORDER BY Population DESC

When you put the SQL keyword DESC after the column name you are ordering by, the result will be ordered in descending order. If you use the ASC keyword instead or you omit the ASC and DESC then the results will be ordered ascending by default.

You can order by more than one column. For example you might want to order by the Continent column first and then by the Population column. You can accomplish that with the following statement:

SELECT Country, Continent, Capital, Population
FROM Countries
ORDER BY Continent, Population

You just list all columns you want to order by separating them with commas. The result will be the following:

Country Continent Capital Population
Italy Europe Rome 58,133,509
UK Europe London 60,609,153
Canada North America Ottawa 33,098,932
Mexico North America Mexico 107,449,525
USA North America Washington 298,444,215