Data is often redundant, so queries often return redundant information. For example, say we were interested in listing all the publishers that are represented in our catalogue. If we select the Publisher from the Works table, we get this:

We can eliminate the redundant output to make the result more readable by adding the DISTINCT keyword to our query:

If we select more than one column—for example, both the place and publisher—then the distinct pairs of values are returned:

Notice in both cases that duplicates are removed even if they didn't appear to be adjacent in the database. Again, it's important to remember that rows aren't actually ordered: they're just displayed that way.

Challenge

1. Write a query that displays all the distinct years in which items were purchased for the library. Hint, look at the Aquired column in the Items table.

As we mentioned earlier, database records are not stored in any particular order. This means that query results aren't necessarily sorted, and even if they are, we often want to sort them in a different way, e.g., in alphabetical order instead of the order in which they were written into the database. We can do this in SQL by adding an ORDER BY clause to our query:

By default, results are sorted in ascending order (i.e., from least to greatest or for A to Z). We can sort in the opposite order using DESC (for "descending"):

(And if we want to make it clear that we're sorting in ascending order, we can use ASC instead of DESC.)

We can also sort on several fields at once. For example, this query sorts the Works table first by Date (in descending order), then by Publisher in ascending order:

Challenge

1. Write a query that displays the Personal and Family name of the authors in the Authors table, ordered by Family name.