In a previous couple of articles, we have seen how to query data from a table. The retrieved rows aren’t printed in any particular order. The ORDER BY clause allows us to print the retrieved rows in an orderly fashion based on the criteria we specify.
Example Syntax :
SELECT col1, col2, ... coln
FROM table
WHERE col3 LIKE "%some-string%"
ORDER BY col3
-- Query 1
SELECT * FROM Actors ORDER BY FirstName;
-- Query 2
SELECT * FROM Actors ORDER BY FirstName DESC;
-- Query 3
SELECT * FROM Actors ORDER BY NetWorthInMillions, FirstName;
-- Query 4
SELECT * FROM Actors ORDER BY NetWorthInMillions, SecondName;
-- Query 5
SELECT * FROM Actors ORDER BY NetWorthInMillions DESC, FirstName ASC;
-- Query 6
SELECT * FROM Actors ORDER BY NetWorthInMillions DESC, FirstName DESC;
-- Query 7
SELECT * FROM Actors ORDER BY BINARY FirstName;
-- Query 8
SELECT * FROM Actors ORDER BY NetWorthInMillions;
-- Query 9
SELECT * FROM Actors ORDER BY CAST(NetWorthInMillions AS CHAR);
This Articles Contents
SELECT * FROM table ORDER BY column1;
Suppose we want to print the names of all the actors sorted in alphabetical order. We can do so using the following query:
SELECT * FROM Actors ORDER BY FirstName;
The ORDER BY clause is followed by the column name on which we intend to sort. This column is called the sort key. By default, the sorting is case-insensitive and in ascending order. Sorting of string columns depends on the character set being used and the collation order.
SELECT * FROM table ORDER BY column DESC;
We can also print the rows in descending order as the following query demonstrates:
SELECT * FROM Actors ORDER BY FirstName DESC;
SELECT * FROM table ORDER BY column1, column2;
We can also specify more than one sort key. In this case if a tie occurs based on the first sort key, it is broken using the second sort key. For example, some actors in our table have the same net worth. We can specify net worth as the first sort key and the first name as the second sort key as follows:
SELECT * FROM Actors ORDER BY NetWorthInMillions, FirstName;
Note the highlighted lines above show Brad Pitt first and Jennifer Aniston second even though both have the same net worth as the tie is broken by the second sort key which is the first name. If we make the second sort key second name, then the row for Jennifer Aniston will rank higher as shown below:
SELECT * FROM Actors ORDER BY NetWorthInMillions, SecondName;
SELECT * FROM table ORDER BY column1 DESC, column2 ASC;
We can also control the ascending or descending order we desire for each sort key. Consider the following query:
SELECT * FROM Actors ORDER BY NetWorthInMillions DESC, FirstName ASC;
Note that Kylie Jenner now ranks at the top with a net worth of 1 billion USD. The rows are first sorted in descending order based on net worth of the actors. The highlighted rows show Brad and Jennifer who are tied on net worth. The second sort key decides who ranks higher and as the letter B occurs before the letter J, the row for Brad Pitt is shown first. If we change the sort order for the second sort key to descending, then the row for Jennifer Aniston will rank higher as shown below:
SELECT * FROM Actors ORDER BY NetWorthInMillions DESC, FirstName DESC;
Sort order is undefined in case sort keys have the same value for rows.
SELECT * FROM table ORDER BY BINARY column;
MySQL ignores the case when comparing strings in the ORDER BY clause, which implies strings “Kim”, “Klm” and “kim” are treated equally. If we want ASCII comparison we need to specify the BINARY keyword before the sort key. To demonstrate the effect of the BINARY keyword, execute the following query and observe the results:
SELECT * FROM Actors ORDER BY BINARY FirstName;
Note that the row for Priyanka Chopra occurs last because upper case letters appear before lower case letters in ASCII ordering.
SELECT * FROM table ORDER BY column;
The CAST function can also be used with the ORDER BY clause. The CAST function allows us to treat a column as a different type. For example, the Actors table can be sorted on the NetWorthInMillions as follows:
SELECT * FROM Actors ORDER BY NetWorthInMillions;
The NetWorthInMillions column is sorted numerically from smallest to largest. We can also sort the NetWorthInMillions column as if strings using the CAST function as follows:
SELECT * FROM Actors ORDER BY CAST(NetWorthInMillions AS CHAR);