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 |