sql

# 3. Sorting Retrieved Data

---[SIDEBAR]---
"type":"objectives",
"title":"Learning Objectives",
"text":"After completing this lesson you can:

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.

"
---[SIDEBAR]---

---[SIDEBAR]---
"type":"tip",
"title":"Improving the Accuracy of AI Responses",
"text":"The examples in the last lesson, used ^ai_name^ to help write SQL statements. At times, ^ai_name^ made guesses about elements of the database that was queried. This caused some the table and column names in the SQL statements to be not quite right. You can improve the accuracy of the SQL statements provided by ^ai_name^ by providing information about the database to be queried. One way to do this would be to give ^ai_name^ the SQL statements used to create the database. The following chat demonstrates how to do this. You can copy and paste the intial prompt in the chat to start your own chat. You will learn how to write the SQL statements needed to create tables in a later lesson."

---[SIDEBAR]---

---[AI]---
[
{
"prompt": "Consider the following table creation statements:

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);\n\nCREATE TABLE subject (work_id INT PRIMARY KEY, subject VARCHAR(100), FOREIGN KEY (work_id) REFERENCES work(work_id));\n\nCREATE TABLE work (work_id INT PRIMARY KEY,name VARCHAR(255),artist_id INT,style VARCHAR(25),museum_id INT,FOREIGN KEY (artist_id) REFERENCES artist(artist_id),FOREIGN KEY (museum_id) REFERENCES museum(museum_id));\n\nCREATE TABLE museum (museum_id INT PRIMARY KEY,name VARCHAR(25),address VARCHAR(100),city VARCHAR(25),state VARCHAR(25),postal CHAR(2),country VARCHAR(25),phone CHAR(10),url VARCHAR(255));\n\nCREATE TABLE museum_hours (museum_id INT,day VARCHAR(255),open TIME,close TIME,FOREIGN KEY (museum_id) REFERENCES museum(museum_id));",
"pretext": "These table creation statements define the structure of a relational database for managing information about artists, their works, subjects, and museums.",
"response": "",
"posttext": "These tables are interrelated through primary key and foreign key relationships to store and retrieve information about artists, their works, subjects, and associated museums."
}
]
---[AI]---

## Sorting Data

In the last lesson, you used a chat with ^ai_name^ to create a SQL statement to list the names of the museums in the database. Notice that after providing ^ai_name^ with information about the database, the SQL statement returned that lists the museum names has the correct table and column names.

---[AI]---
[
{
"prompt": "Write a SQL statement to list the names of museums.",
"pretext": "To list the names of museums from the \"museum\" table, you can use the following SQL statement:",
"response": "SELECT name \nFROM museum;",
"posttext": "This query selects the \"name\" column from the \"museum\" table, retrieving the names of all museums stored in the table."
}
]
---[AI]---

As you saw in the last lesson, the SQL statement below will display the names of the museums from the museum table.

*SELECT name
FROM museum*

**Try the SQL statement.**

---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas|visible
owner:atlas-query
result:museum-name
---[SQL]---
SELECT name
FROM museum;
---[SQL]---

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

---[SIDEBAR]---
"type":"term",
"title":"CLAUSE",
"text":"As clause of a SQL statement is an element of the 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 all SELECT statements."
---[SIDEBAR]---

To define the sorting order of the rows that are retrieved using a SELECT statement, you use the ORDER BY clause. The ORDER BY clause is used to define one or more columns on which the data returned by the query will be sorted.

---[SIDEBAR]---
"type":"term",
"title":"ORDER BY",
"text":"The ORDER BY clause is used to define one or more columns on which the data returned by a SELECT statement will be sorted."
---[SIDEBAR]---

You may want to arrange and display the museum names in the last query in alphabetical order. You can use ^ai_name^ to adjust the SELECT statement.

---[AI]---
[
{
"prompt": "Write a SQL statement to list the names of museums in alphabetical order.",
"pretext": "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:",
"response": "SELECT name \nFROM museum \nORDER BY name ASC;",
"posttext": "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."
}
]
---[AI]---

You may want to arrange and display the museum names in the last query in alphabetical order. To do so, adjust the SELECT statement to:

*SELECT name
FROM museum
ORDER BY name ASC*

**Try the SQL statement.**

---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT name
FROM museum
ORDER BY name ASC;
---[SQL]---

---[SIDEBAR]---
"type":"caution",
"title":"Where to place the ORDER BY Clause",
"text":"When you use an ORDER BY clause in a SELECT statement, it must always be last. Placing ORDER BY before any of the other clauses (SELECT, FROM, WHERE, GROUP BY, or HAVING) will result in an error."
---[SIDEBAR]---

Notice that the only difference between this SELECT statement and the one in the previous example is the ORDER BY clause. The ORDER BY clause specifies the column or columns that will be used for sorting and instructions for how the sorting should be done. In this case, the query results will be sorted by the name column in ascending order (notice the keyword ASC). Ascending order for a column that contains text (like museum name) means alphabetical order.

The default option for sorting data in a column is to do so in ascending order. Because of this, it is actually not necessary to include the keyword ASC in the statement. In fact the keyword ASC is omitted in most cases. It is just as correct, and more common, to use this syntax:

*SELECT name
FROM museum
ORDER BY name*

**Try the SQL statement.**

---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT name
FROM museum
ORDER BY name;
---[SQL]---

## Setting the Sort Direction

When you sort columns that contain text, ascending order means that the data in the column will be sorted in alphabetical (A-Z) order. You can also sort columns that contain other types of data. When a column that contains numbers is sorted in ascending order, the values are arranged smallest to largest. A column that contains dates that is sorted in ascending order will be arranged from the oldest to the newest.

---[SIDEBAR]---
"type":"term",
"title":"Ascending (ASC)",
"text":"When data is sorted in ascending order (ASC) it is arranged alphabetically for text, smallest to largest for numbers, and from the least to the most current for dates."
---[SIDEBAR]---

You can also choose to reverse the sorting order of a column or sort the data in a column in descending order. To sort data in descending order, you use the keyword DESC. Since this is not the default way to sort, you must include the keyword DESC in the ORDER BY clause if you wish to reverse the sorting of the data.

When you sort text in descending order, the data will be arranged in reverse alphabetical order (Z-A). Sorting numbers in descending order will arrange them from largest to smallest. Finally, sorting dates in descending order will put the most current date first and the least current date last.

---[SIDEBAR]---
"type":"term",
"title":"Descending (DESC)",
"text":"When data is sorted in descending order (DESC) it is arranged reverse alphabetically for text, largest to smallest for numbers, and from the most to the least current for dates."
---[SIDEBAR]---

What if you want to arrange and display the museum names in the last query in reverse alphabetical order. You can use ^ai_name^ to adjust the SELECT statement.

---[AI]---
[
{
"prompt": "Adjust the SQL statement to list the museum names in reverse alphabetical order.",
"pretext": "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:",
"response": "SELECT name \nFROM museum \nORDER BY name DESC;",
"posttext": "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."
}
]
---[AI]---

What if you want to arrange and display the museum names in the last query in reverse alphabetical order. This would require an adjustment the SELECT statement to:

*SELECT name
FROM museum
ORDER BY name DESC*

**Try the SQL statement.**

---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-sorted
---[SQL]---
SELECT name
FROM museum
ORDER BY name DESC;
---[SQL]---

---[SIDEBAR]---
"type":"caution",
"title":"Sorting Text and Case Sensitivity",
"text":"When sorting text, capitalization can be problematic. For example, it is possible that an upper-case A will be treated the same as a lower-case a. When this happens, would the letter 'a' be sorted before or after the letter 'Z'? In most cases the DBMS will treat upper and lower case letters the same (A will be treated the same as a). However, there may be situations where this is not desirable and the database administrator configures the DBMS to behave differently. When this is the case, you may find that sorting text gives you some unexpected results and you may not be able to achieve the result you are looking for with a simple ORDER BY clause."
---[SIDEBAR]---

## Sorting by Multiple Columns

Generally, a query will include more than one column. For example, you may wish to see the city and country for each museum in the alphabetized list of museum names. You can use ^ai_name^ to create the SELECT statement for such a query.

---[AI]---
[
{
"prompt": "Write a SQL statement to show an alphabetized list of names, cities, and countries of museums.",
"pretext": "To display an alphabetized list of names, cities, and countries of museums from the \"museum\" table, you can use the following SQL statement:",
"response": "SELECT name, city, country \nFROM museum \nORDER BY name ASC;",
"posttext": "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."
}
]
---[AI]---

Generally, a query will include more than one column. For example, you may wish to see the city and country for each museum in the alphabetized list of museum names. The SELECT statement for this query would be:

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

---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-city-country-sorted-by-name
---[SQL]---
SELECT name, city, country
FROM museum
ORDER BY name;
---[SQL]---

**Try the SQL statement.**

Recall that all of the information for a row corresponds with the same museum. For example, the first row of the example query contains information about the Army Museum which is in Paris, France. If the data were to be sorted in reverse alphetical order by name not only would the Army Museum be displayed as the last row of the query result, but the Paris and France would also move with it. In other words, data on the same row will be rearranged together even if the query is only sorted on one column.

Additionally, it is possible to sort data by more than one column. For example, you may wish to to see the list of museums arranged by country and then by city within each country. This would provide a geographical arrangement of the museum data. You can use ^ai_name^ to create a SELECT statement to create this query.

---[AI]---
[
{
"prompt": "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.",
"pretext": "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:",
"response": "SELECT name, city, country \nFROM museum \nORDER BY country ASC, city ASC;",
"posttext": "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."
}
]
---[AI]---

Additionally, it is possible to sort data by more than one column. For example, you may wish to to see the list of museums arranged by country and then by city within each country. This would provide a geographical arrangement of the museum data. The SELECT statement for this query would be:

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

If you were to sort a column in descending order, you would still use the DESC keyword. Note that the DESC keyword only affects the column right before it in the ORDER BY clause. You can use ^ai_name^ to write a SELECT statement for this query.

---[AI]---
[
{
"prompt": "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.",
"pretext": "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:",
"response": "SELECT name, city, country \nFROM museum \nORDER BY country DESC, city ASC;",
"posttext": "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."
}
]
---[AI]---

If you were to sort a column in descending order, you would still use the DESC keyword. Note that the DESC keyword only affects the column right before it in the ORDER BY clause. The SELECT statement for this query would be:

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

**Try the SQL Statement.**

---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-city-country-sorted
---[SQL]---
SELECT name, city, country
FROM museum
ORDER BY country DESC, city;
---[SQL]---

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. Also notice that 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 example Country. City is not affected by the DESC keyword, so the query result is sorted by city in ascending or alphabetical order.

---[SIDEBAR]---
"type":"caution",
"title":"Sorting Multiple Columns in Descending Order",
"text":"Make sure you include the keyword DESC after each column you wish to be sorted in descending order. Otherwise, the columns without the keyword DESC will be sorted in ascending order."
---[SIDEBAR]---

---[SIDEBAR]---
"type":"note",
"title":"Sorting by Columns Not in the SELECT Clause.",
"text":"Generally, the columns you use in the ORDER BY clause will also be listed in the SELECT clause to be shown in the query result. This is not required, however. You can sort by any column on the table, regardless of whether the columns is displayed in the query result and therefore listed in the SELECT clause."
---[SIDEBAR]---

## Sorting by Column Position

All of the examples of the ORDER BY clause have used column names for sorting. ORDER BY will also accept the position number of the columns in the SELECT clause in place of column names for sorting. In a previous example you selected the name, city, and country of the museums on the museum table in the database. These names were then arranged by country and then city, both in ascending (or alphabetical) order. The SELECT statement for this query is:

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

This syntax can be modified to refer to column numbers in the ORDER BY clause rather than column names. The SELECT statement would be:

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

**Try the SQL Statement.**
---[SQL]---
connection:sql_book_connection
schema:canvas
diagram:canvas
owner:atlas-query
result:museum-name-city-country-sorted-3-2
---[SQL]---
SELECT name
,city
,country
FROM museum
ORDER BY 3, 2
---[SQL]---

Notice that the query results in the data being sorted first by country and then by city. Also, note the positioning of the columns in the SELECT clause. Name is listed first, city second, and country third. In the updated syntax, the ORDER BY clause is set to sort by column 3 (country) and then column 2 (city). This means that both queries will display the data identically.

Using column position rather than column names in the ORDER BY clause has one main advantage and some disadvantages. The main advantage is that you don't need to retype the column names in the ORDER BY clause after already typing them in the SELECT clause. Unfortunately, this approach increases the likelihood of choosing the wrong column for sorting. Also, if you change the order of the columns in the SELECT clause, you might forget to update the column numbers in the ORDER BY clause. Moreover, this approach makes the SQL statement a little harder to read. Finally, you cannot use the column position to sort data on a column that is not listed in the SELECT clause.

---[SIDEBAR]---
"type":"tip",
"title":"Sorting by Column Names and Column Numbers.",
"text":"You can use a combination of column names and column numbers in the same ORDER BY clause."
---[SIDEBAR]---

## 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. It can also be sorted on multiple columns.