Databases and SQL

Sorting and Removing Duplicates

In beginning our examination of the Antarctic data, we want to know:

• what types of measurements were taken at each site;
• which scientists took measurements on the expedition;
• where each scientist took each measurement

Selecting unique data

We can examine the Survey table to determine which measurements were taken at each site. Data is often redundant, so queries often return redundant information. For example, if we select the measured quantities from the Survey table, we get this:

SELECT quant FROM Survey;

This result makes it difficult to see the unique types of quant listed in the Survey table. We can eliminate the redundant output to make the result more readable by adding the DISTINCT keyword to our query:

SELECT DISTINCT quant FROM Survey;

If we want to determine where each type of measurement was collected, we can use the DISTINCT keyword on multiple columns. If we select two columns, the DISTINCT keyword returns the unique pairs of values in those columns:

SELECT DISTINCT taken, quant FROM Survey;

Notice in both cases that duplicates are removed even if the rows they come from didn’t appear to be adjacent in the database table.

Sorting data

Our next task is to identify the scientists that were on the expedition. Their names are listed in the Person table. As we mentioned earlier, database records are not stored in any particular order. This means that query results aren’t necessarily sorted in any useful way, and even if they are, we might want to sort them differently, e.g., by their identifier instead of by their personal name. We can do this in SQL by adding an ORDER BY clause to our query:

SELECT * FROM Person ORDER BY ident;

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

SELECT * FROM Person ORDER BY ident DESC;

(And if we want to make it explicit 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 results from the ‘Survey’ table first in ascending order by taken, and then in descending order by person within each group of equal taken values:

SELECT taken, person, quant FROM Survey ORDER BY taken ASC, person DESC;

The field person is an identifier for the scientist who took each measurement, so this query gives us a good idea of which scientist was at which site and what measurements they performed while they were there.

Combining clauses

Looking at the table, it seems like some scientists specialized in certain kinds of measurements. We can examine which scientists performed which measurements by selecting the appropriate columns and removing duplicates.

SELECT DISTINCT quant, person FROM Survey ORDER BY quant ASC;