Sorting and Removing Duplicates

Objectives

  • Write queries that display results in a particular order.
  • Write queries that eliminate duplicate values from data.

Data is often redundant, so queries often return redundant information. For example, if we select the quantitites that have been measured from the survey table, we get this:


In [1]:
%load_ext sqlitemagic

In [2]:
%%sqlite survey.db
select quant from Survey;


rad
sal
rad
sal
rad
sal
temp
rad
sal
temp
rad
temp
sal
rad
sal
temp
sal
rad
sal
sal
rad

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


In [3]:
%%sqlite survey.db
select distinct quant from Survey;


rad
sal
temp

If we select more than one column—for example, both the survey site ID and the quantity measured—then the distinct pairs of values are returned:


In [4]:
%%sqlite survey.db
select distinct taken, quant from Survey;


619rad
619sal
622rad
622sal
734rad
734sal
734temp
735rad
735sal
735temp
751rad
751temp
751sal
752rad
752sal
752temp
837rad
837sal
844rad

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.

Challenges

  1. Write a query that selects distinct dates from the Site 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., by the name of the project instead of by the name of the scientist. We can do this in SQL by adding an order by clause to our query:


In [5]:
%%sqlite survey.db
select * from Person order by ident;


danforthFrankDanforth
dyerWilliamDyer
lakeAndersonLake
pbFrankPabodie
roeValentinaRoerich

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


In [6]:
%%sqlite survey.db
select * from person order by ident desc;


roeValentinaRoerich
pbFrankPabodie
lakeAndersonLake
dyerWilliamDyer
danforthFrankDanforth

(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 results first in ascending order by taken, and then in descending order by person within each group of equal taken values:


In [7]:
%%sqlite survey.db
select taken, person from Survey order by taken asc, person desc;


619dyer
619dyer
622dyer
622dyer
734pb
734pb
734lake
735pb
735None
735None
751pb
751pb
751lake
752roe
752lake
752lake
752lake
837roe
837lake
837lake
844roe

This is easier to understand if we also remove duplicates:


In [8]:
%%sqlite survey.db
select distinct taken, person from Survey order by taken asc, person desc;


619dyer
622dyer
734pb
734lake
735pb
735None
751pb
751lake
752roe
752lake
837roe
837lake
844roe

Challenges

  1. Write a query that returns the distinct dates in the Visited table.

  2. Write a query that displays the full names of the scientists in the Person table, ordered by family name.

Key Points

  • The records in a database table are not intrinsically ordered: if we want to display them in some order, we must specify that explicitly.
  • The values in a database are not guaranteed to be unique: if we want to eliminate duplicates, we must specify that explicitly as well.