websql

5 Sorting Retrieved Data

Learning Objectives

:

Use the ORDER BY clause of a SELECT statement to sort the rows in a query result.

Sort the rows of a query result in both ascending and descending order.

Sort the rows of a query result by more than one column in the data.

Use the keyword LIMIT to show a specified number of rows in a query result.

Improving the Accuracy of AI Responses. The examples in the last lesson used an AI assistant to help write SQL statements. At times, the assistant made guesses about elements of the database being queried, which caused some of the table and column names in the SQL statements to be not quite right. You can improve the accuracy of the SQL statements an AI assistant gives you by providing information about the database up front. One way to do this is to give the assistant the SQL statements used to create the tables. The chat below demonstrates this. You can copy and paste the initial prompt to start a chat of your own — you'll learn how to write the SQL statements needed to create tables in a later lesson.

Consider the following table creation statements: ```sql CREATE TABLE artist ( artist_id INT PRIMARY KEY, full_name VARCHAR(50), first_name VARCHAR(25), middle_names VARCHAR(50), last_name VARCHAR(25), nationality VARCHAR(25), style VARCHAR(25), birth INT, death INT ); CREATE TABLE museum ( museum_id INT PRIMARY KEY, name VARCHAR(25), address VARCHAR(100), city VARCHAR(25), state VARCHAR(25), postal_code VARCHAR(10), country VARCHAR(25), phone CHAR(10), url VARCHAR(255) ); CREATE TABLE work ( work_id INT PRIMARY KEY, name VARCHAR(255), artist_id INT, style VARCHAR(25), museum_id INT, width INT, height INT, FOREIGN KEY (artist_id) REFERENCES artist(artist_id), FOREIGN KEY (museum_id) REFERENCES museum(museum_id) ); CREATE TABLE subject ( work_id INT NOT NULL, subject VARCHAR(100), FOREIGN KEY (work_id) REFERENCES work(work_id) ); CREATE TABLE museum_hours ( museum_id INT, day VARCHAR(255), open TIME, close TIME, FOREIGN KEY (museum_id) REFERENCES museum(museum_id) ); ```send

These table creation statements define the structure of a relational database for managing information about artists, their works, subjects, and museums. These tables are interrelated through primary key and foreign key relationships to store and retrieve information about artists, their works, subjects, and associated museums.

5.1 Sorting Data

In the last lesson, you used a chat with an AI assistant to create a SQL statement listing the names of the museums in the database. Now that the assistant has been given information about the database, the SQL statement it returns for that request already has the correct table and column names:

Write a SQL statement to list the names of museums.send

To list the names of museums from the "museum" table, you can use the following SQL statement:
SELECT name
FROM museum;
This query selects the "name" column from the "museum" table, retrieving the names of all museums stored in the table.

Try the SQL statement.

SELECT name
FROM   museum;

Notice that the museum names are displayed in what appears to be a random order. Generally, data is displayed in the order it is stored in the database table, which is often the order it was added to the table. However, once data is updated or deleted, the DBMS may store the rows in a different physical order. The only way to reliably predict the order in which rows are retrieved is to intentionally define how you want the data sorted as part of the SQL statement. This is done with the ORDER BY clause of the SELECT statement.

Clause — An element of a SQL statement that defines a specific aspect of the statement. The SELECT statement has six clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. Of these, only SELECT and FROM are required in every SELECT statement.

To define the sort order of the rows retrieved by a SELECT statement, you use the ORDER BY clause, which specifies one or more columns to sort the returned data on.

ORDER BY — The clause used to define one or more columns on which the data returned by a SELECT statement will be sorted.

Say you want to take the last query and arrange the museum names alphabetically. You can use a chat with an AI assistant to adjust the SELECT statement:

Write a SQL statement to list the names of museums in alphabetical order.send

To list the names of museums in alphabetical order, you can modify the previous SQL statement by adding an ORDER BY clause. Here's the updated SQL statement:
SELECT name
FROM museum
ORDER BY name ASC;
This query selects the "name" column from the "museum" table and uses the ORDER BY clause to sort the results in ascending order (A-Z) based on the museum names. The result will be a list of museum names listed in alphabetical order.

This query produces the correct result. It displays the name column from the museum table, and the ORDER BY clause sorts the rows in the query result. The keyword ASC sorts the rows in ascending order; since name is a text column, sorting it in ascending order returns an alphabetized list.

Try the SQL statement.

SELECT   name
FROM     museum
ORDER BY name ASC;

This is one of the book's interactive query boxes (tied to the artist/work/museum schema diagram, with a "Play" button and results panel) — shown here as a plain code block until that component is built on this platform.

Where to Place the ORDER BY Clause. When you use an ORDER BY clause in a SELECT statement, it must always come last. Placing ORDER BY before any of the other clauses (SELECT, FROM, WHERE, GROUP BY, or HAVING) will result in an error.

The default way to sort data in a column is ascending order, so it's actually unnecessary to include the keyword ASC. In fact, ASC is omitted in most cases. It is just as correct, and more common, to write:

SELECT   name
FROM     museum
ORDER BY name;

5.2 Setting the Sort Direction

When you sort a column that contains text, ascending order means the data is arranged alphabetically (A-Z). You can sort other kinds of data too. Sorting a column of numbers in ascending order arranges the values from smallest to largest, and sorting a column of dates in ascending order arranges them from oldest to newest.

Ascending (ASC) — When data is sorted in ascending order, it is arranged alphabetically for text, smallest to largest for numbers, and least to most recent for dates.

You can also reverse the sort order of a column by sorting it in descending order, using the keyword DESC. Since descending is not the default, you must always include DESC in the ORDER BY clause to reverse the sort.

Sorting text in descending order arranges it in reverse alphabetical order (Z-A). Sorting numbers in descending order arranges them from largest to smallest, and sorting dates in descending order puts the most recent date first and the least recent date last.

Descending (DESC) — When data is sorted in descending order, it is arranged in reverse alphabetical order for text, largest to smallest for numbers, and most to least recent for dates.

Say you want to arrange and display the museum names in reverse alphabetical order. You can use a chat with an AI assistant to adjust the SELECT statement:

Adjust the SQL statement to list the museum names in reverse alphabetical order.send

To list the museum names in reverse alphabetical order (Z-A), you can modify the SQL statement by using the DESC keyword in the ORDER BY clause. Here's the adjusted SQL statement:
SELECT name
FROM museum
ORDER BY name DESC;
This query selects the "name" column from the "museum" table and uses the ORDER BY clause to sort the results in descending order (Z-A) based on the museum names. The result will be a list of museum names listed in reverse alphabetical order.

This query produces the correct result. It displays the museum names from the museum table, and the keyword DESC ensures the results are sorted in reverse alphabetical order.

Try the SQL statement.

SELECT   name
FROM     museum
ORDER BY name DESC;

Sorting Text and Case Sensitivity. When sorting text, capitalization can be problematic. It's possible for an upper-case A to not be treated the same as a lower-case a — in which case, would the letter "a" sort before or after the letter "Z"? PostgreSQL's default collation generally treats upper- and lower-case letters as equivalent for sorting purposes, ordering primarily by letter and only using case to break ties. However, a database can be configured with a different collation that sorts case differently. When this is the case, sorting text may give you unexpected results that a simple ORDER BY clause can't fix.

5.3 Sorting by Multiple Columns

Most queries include more than one column, and you can still sort the results of such a query. For example, say you want to see the city and country for each museum alongside the alphabetized list of museum names. You can use a chat with an AI assistant to create the SELECT statement for this query:

Write a SQL statement to show an alphabetized list of names, cities, and countries of museums.send

To display an alphabetized list of names, cities, and countries of museums from the "museum" table, you can use the following SQL statement:
SELECT name, city, country
FROM museum
ORDER BY name ASC;
This query selects the "name", "city", and "country" columns from the "museum" table. The ORDER BY clause is used to sort the results in ascending order based on the museum names. The result will be an alphabetized list of museum names, along with their corresponding cities and countries.

This query produces the correct result. It lists the name, city, and country columns from the museum table, with the rows sorted in ascending order by museum name.

Try the SQL statement.

SELECT   name, city, country
FROM     museum
ORDER BY name;

Recall that each row of a database table contains all of the information stored about a single instance of that table. For example, the first row of the query above contains information about the Army Museum, which is in Paris, France. If the data were instead sorted in reverse alphabetical order by name, the Army Museum would move to the last row of the result — and Paris and France would move right along with it. In other words, the data on a row is rearranged together, even when the query is only sorted on one of its columns.

It's also possible to sort by more than one column. For example, say you want to see the list of museums arranged by country and then by city within each country, giving the result a geographical arrangement. You can use a chat with an AI assistant to create the SELECT statement for this query:

Write a SQL statement to show a list of names, cities, and countries of museums. Sort the results first by country and then by city.send

To display a list of names, cities, and countries of museums from the "museum" table, sorted first by country and then by city, you can use the following SQL statement:
SELECT name, city, country
FROM museum
ORDER BY country ASC, city ASC;
This query selects the "name", "city", and "country" columns from the "museum" table. The ORDER BY clause is used to sort the results first in ascending order based on the country column and then in ascending order based on the city column. The result will be a list of museum names, cities, and countries, sorted first by country and then by city.

This query produces the correct result. It lists the name, city, and country columns from the museum table. The results are first sorted by country, then by city, with both columns sorted in alphabetical (ascending) order.

Try the SQL statement.

SELECT   name, city, country
FROM     museum
ORDER BY country, city;

The order of the columns listed in the ORDER BY clause matters. Data is sorted first by the first column listed, and then by each successive column. Sorting by country first and then city produces a very different arrangement than sorting by city first and then country. Notice that sorting by country and then city arranges the museums by country, and when there is more than one museum in a country, those museums are then arranged alphabetically by city within that country.

If you want to sort more than one column in descending order, each of those columns must include the DESC keyword — DESC only affects the column that comes directly before it in the ORDER BY clause. Say you want to list the name, city, and country from the museum table, ordering the rows in reverse alphabetical order by country and then alphabetically by city. You can use a chat with an AI assistant to write the SELECT statement:

Write a SQL statement to show a list of names, cities, and countries of museums. Sort the results first by country in reverse alphabetical order and then alphabetically by city.send

To display a list of names, cities, and countries of museums from the "museum" table, sorted first by country in reverse alphabetical order and then alphabetically by city, you can use the following SQL statement:
SELECT name, city, country
FROM museum
ORDER BY country DESC, city ASC;
This query selects the "name", "city", and "country" columns from the "museum" table. The ORDER BY clause is used to sort the results first in descending order (reverse alphabetical) based on the country column and then in ascending order (alphabetically) based on the city column. The result will be a list of museum names, cities, and countries, sorted first by country in reverse alphabetical order and then alphabetically by city.

The query syntax is correct. It lists the name, city, and country columns from the museum table, and the ORDER BY clause correctly specifies that the rows will first be sorted by country in reverse alphabetical order and then by city in alphabetical order.

Try the SQL statement.

SELECT   name, city, country
FROM     museum
ORDER BY country DESC, city;

Notice that the museums in the USA are arranged at the top of the query result, because USA is the last country alphabetically on the museum table, and the cities within the USA are arranged alphabetically. This happens because the keyword DESC only applies to the column directly before it in the ORDER BY clause — in this case, country. city is not affected by DESC, so it is sorted in ascending, alphabetical order.

Sorting Multiple Columns in Descending Order. Be sure to include the keyword DESC after each column you want sorted in descending order. Columns without DESC will be sorted in ascending order.

Sorting by Columns Not in the SELECT Clause. Generally, the columns you use in the ORDER BY clause will also be listed in the SELECT clause so they appear in the query result. This isn't required, however — you can sort by any column on the table, whether or not that column is displayed in the query result.

5.4 Sorting by Column Position

Every example of the ORDER BY clause so far has used column names for sorting, but ORDER BY will also accept the position number of a column in the SELECT clause in place of its name. Recall the earlier example that selected the name, city, and country of the museums, arranged by country and then city, both in ascending order:

SELECT   name
        ,city
        ,country
FROM     museum
ORDER BY country, city;

This can be rewritten to refer to column numbers in the ORDER BY clause instead of column names:

SELECT   name
        ,city
        ,country
FROM     museum
ORDER BY 3, 2;

Both queries sort the data identically — first by country and then by city. Notice the position of the columns in the SELECT clause: name is listed first, city second, and country third. In the rewritten query, ORDER BY 3, 2 sorts by column 3 (country) and then column 2 (city).

Using column position instead of column names in the ORDER BY clause has one main advantage and several disadvantages. The advantage is that you don't have to retype the column names in the ORDER BY clause after already typing them in the SELECT clause. Unfortunately, this approach makes it easier to sort by the wrong column, and if you later change the order of the columns in the SELECT clause, you might forget to update the numbers in the ORDER BY clause. It also makes the statement a little harder to read, and you can't use a column position to sort by a column that isn't listed in the SELECT clause.

Sorting by Column Names and Column Numbers. You can use a combination of column names and column numbers in the same ORDER BY clause.

5.5 Limiting Results

There are times when you want to retrieve only the first row, or first set of rows, from a table. In PostgreSQL, this is done with the keyword LIMIT.

LIMIT. The keyword LIMIT always appears as the last clause of a SELECT statement — after ORDER BY, if one is present.

Say you want to retrieve the first five rows of the museum table. The SQL statement used to do this is:

SELECT *
FROM museum
LIMIT 5;

All columns are retrieved from the museum table, and LIMIT 5 ensures that only the first five rows of the result are displayed.

Since no sort order is specified for this query, there's no way to know in advance which five rows will be returned. For this reason, LIMIT is almost always applied to a query that is also sorted. When LIMIT and ORDER BY are used together, the query displays the rows with either the top (largest) or bottom (smallest) values for a column. For example, say you want to retrieve all of the information for the five artists who were born most recently. You can use a chat with an AI assistant to write the SELECT statement for this query:

Write a SQL statement to show all of the columns from the artist table for the five artists who were born most recently.send

To retrieve all columns from the artist table for the five artists who were born most recently, you can use the ORDER BY clause with the DESC (descending) option to sort the results by birth year in reverse order, and then limit the result set to the top five rows using the LIMIT clause. Here's the SQL query to achieve that:
SELECT *
FROM artist
ORDER BY birth DESC
LIMIT 5;
This query selects all columns from the artist table, orders the result set by birth year in descending order (most recent first), and then limits the output to the top five rows using LIMIT 5.

This query produces the correct result. All columns are retrieved from the artist table, sorted by birth in descending order so the most recently born artists come first, and LIMIT 5 ensures that only those first five rows are displayed.

Try the SQL statement.

SELECT *
FROM   artist
ORDER BY birth DESC
LIMIT  5;

The Standard SQL Alternative. PostgreSQL also supports the SQL-standard syntax FETCH FIRST n ROWS ONLY as an alternative to LIMIT. The two are interchangeable — SELECT * FROM museum FETCH FIRST 5 ROWS ONLY; returns the same result as SELECT * FROM museum LIMIT 5;. LIMIT is the more common and idiomatic choice in PostgreSQL, so it's the syntax used throughout this book.

5.6 Summary

In this lesson, you learned how to use the ORDER BY clause in the SELECT statement to arrange the rows in a query result. Data can be sorted in ascending or descending order, and on multiple columns at once. Finally, you learned how to use the keyword LIMIT to display only a specified number of rows in a query result.