SQL Order By — Sorting For Readability

Relational databases store information in tables — with columns that are analogous to elements in a data structure and rows which are one instance of that data structure — the order of which is undetermined; in no way guaranteed to reflect the order in which the data was inserted into the table.

The SQL Order By statement generates ordered output that’s of more use to consumers, be they humans reading a chart or a program processing the data. The general format of SQL Order By is:

SELECT column1, column2, … 
FROM table 
ORDER BY column1, column2, ... {ASC|DESC};

Ascending and Descending Via SQL Order By

Because the behind-the-scenes machinations of storing data are hidden from us by the database engine we are not allowed to make assumptions about the order of rows in a table. This needn’t be a drawback as SQL provides ways to select, filter, and sort that data in a variety of ways.

The SQL statements required to create and populate 2019_most_visited_cities_in_millions with data from the most-visited cities in 2019 appears at the end of this blog post.

SELECT * 
FROM 2019_most_visited_cities_in_millions ;

The first few lines look like:

To reverse the sort order, which by default is ASC (ascending), we specify DESC (descending):

SELECT * 
FROM 2019_most_visited_cities_in_millions
ORDER BY position DESC ;

The first few lines look like:

SQL Order By Multiple Columns — A Book Index

Perhaps what’s desired is something like an index for a travelogue. Human readers understand things in alphabetical order, so ordering by country first, then city within country, yields the most useful table output for people browsing travel destinations.

SELECT country, city
FROM 2019_most_visited_cities_in_millions
ORDER BY country, city ;

Note in the following output that the countries are ordered in ascending alphabetical order and within each country which has multiple cities, those cities appear in ascending alphabetical order, exactly as was specified above in the ORDER BY clause. (Rather than showing the entire output table, which will make for a long blog entry, for brevity’s sake here’s partial output showing those countries with multiple city entries.) 

The above is the terse form of SQL Order By, omitting the default values for sort order. The full format of the same statement is:

SELECT country, city, position 
FROM 2019_most_visited_cities_in_millions
ORDER BY country ASC, city ASC ;

SQL Order By & Distinct() — A Table of Contents

Just as we made an index for the back of our travelogue, perhaps we need a table of contents for the front of our tome. Using DISTINCT() to winnow out one entry of each country gives us a starting point.

SELECT DISTINCT(country) 
FROM 2019_most_visited_cities_in_millions
ORDER BY country ;

SQL Group By — Another Table of Contents

In SQL, similarities exist between the ORDER BY and GROUP BY clauses. The contexts in which it is ideal to use each type of clause differ (and beyond the scope of this blog post) but in the interests of completeness we show how  the same table of contents can be generated via the GROUP BY clause.

SELECT country 
FROM 2019_most_visited_cities_in_millions
GROUP BY country ;

The output is the same as the table above.

Conclusion

We’ve covered using SQL Order By to sort relational database tables into human-readable order, as opposed to the unordered way rows may be stored natively within the database. We’ve called out how to sort in ascending and descending order with the ASC and DESC keywords. We’ve shown how to order by multiple columns to generate a book index, and how  to use either DISTINCT() and GROUP BY to generate a table of contents.

Start Learning

To learn more about SQL, enroll in our SQL Nanodegree program, described in detail here.

SQL Order By Sample Table Code

The following will build and populate the sample data referred to in this blog post, data you can test interactively at  SQL Fiddle.

CREATE TABLE 2019_most_visited_cities_in_millions (
positionINT,
cityVARCHAR(33),
countryVARCHAR(33),
visitorsFLOAT ) ;
 
INSERT INTO 2019_most_visited_cities_in_millions
VALUES
( 1, "Bangkok", "Thailand", 22.78 ),
( 2, "Paris", "France", 19.1 ),
( 3, "London", "England", 19.09 ),
( 4, "Dubai", "United Arab Emirates", 15.93 ),
( 5, "Singapore", "Singapore", 14.67 ),
( 6, "Kuala Lumpur", "Malaysia", 13.79 ),
( 7, "New York City", "USA", 13.6 ),
( 8, "Istanbul", "Turkey", 13.4 ),
( 9, "Tokyo", "Japan", 12.93 ),
( 10, "Antalya", "Turkey", 12.41 ),
( 11, "Seoul", "South Korea", 11.25 ),
( 12, "Osaka", "Japan", 10.14 ),
( 13, "Mecca", "Saudi Arabia", 10 ),
( 14, "Phuket", "Thailand", 9.89 ),
( 15, "Pattaya", "Thailand", 9.44 ),
( 16, "Milan", "Italy", 9.10 ),
( 17, "Barcelona", "Spain", 9.09 ),
( 18, "Palma de Mallorca", "Spain", 8.96 ),
( 19, "Bali", "Indonesia", 8.26 ) ;