MySQL and the ORDER BY part

In the previous chapter, we used the WHERE part of an SQL query to decide which rows we wanted to SELECT. In this chapter, we will look into sorting the result, to get the data out in a specific order. In MySQL, as in most other SQL dialects, the keyword used to sort data is ORDER BY. Let's jump straight to an example:

SELECT name, country FROM test_users ORDER BY name

The ORDER BY is followed by the column we wish to sort by. In this example, we get our users sorted alphabetically by their name. The default order is from small to big, or in the case of strings, from A to Z, known as ascending order. In other words, the above example could also look like this:

SELECT name, country FROM test_users ORDER BY name ASC

But since it's the default order, it's optional. If you want from big to small or Z to A, we want it in descending order, for which we use the DESC keyword:

SELECT name, country FROM test_users ORDER BY name DESC

In some cases, it can be very useful to sort by more than one column. For instance, you may want to sort by country first, and name secondly. The syntax is the same, you just separate the columns you wish to sort by with a comma, like this:

SELECT name, country FROM test_users ORDER BY country, name

In this case, users will be ordered by the name of their country first, and people from the same country will be ordered by their own name. You can even use different sorting directions for each of the columns, like this:

SELECT name, country FROM test_users ORDER BY country ASC, name DESC

To test the examples above, you can use this test code we wrote in a previous chapter. Simply put one of the queries inside the mysql_query() function and run the code:

mysql_connect("localhost", "username", "password");
mysql_select_db("my_database");

$query = mysql_query("Insert your SQL query here");
while($row = mysql_fetch_array($query))
    echo $row["name"] . " is from " . $row["country"] . "<br />";