Databases and SQL - Library edition

Sorting and Removing Duplicates

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:

SELECT Publisher FROM Works;

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

SELECT DISTINCT Publisher FROM Works;

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

SELECT DISTINCT Place, Publisher FROM Works;

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.

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:

SELECT DISTINCT Publisher FROM Works ORDER BY Publisher;

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”):

SELECT DISTINCT Publisher FROM Works ORDER BY Publisher DESC;

(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:

SELECT Title, Date, Publisher FROM Works ORDER BY Date DESC, Publisher ASC;