Section 2.4
DISTINCT and Eliminating Duplicates
Let's say that you want to list the ID and names of
only those people who have sold an antique.
Obviously, you want a list where each seller is only listed once--you don't want to know how many
antiques a person sold, just the fact that this person sold one (for counts, see the Aggregate Function
section below). This means that you will need to tell SQL to eliminate duplicate sales rows, and just
list each person only once. To do this, use the
DISTINCT keyword.
First, we will need an equijoin to the AntiqueOwners table to get the detail data of the person's
LastName and FirstName. However, keep in mind that since the SellerID column in the Antiques table
is a foreign key to the AntiqueOwners table, a seller will only be listed if there is a row in the
AntiqueOwners table listing the ID and names. We also want to eliminate multiple occurences of the
SellerID in our listing, so we use
DISTINCT on the column where the repeats may occur.
To throw in one more twist, we will also want the list alphabetized by LastName, then by FirstName
(on a LastName tie), then by OwnerID (on a LastName and FirstName tie). Thus, we will use the
ORDER BY
clause:
SELECT DISTINCT SELLERID, OWNERLASTNAME, OWNERFIRSTNAME
FROM ANTIQUES, ANTIQUEOWNERS
WHERE SELLERID = OWNERID
ORDER BY OWNERLASTNAME, OWNERFIRSTNAME, OWNERID;
In this example, since everyone has sold an item, we will get a listing of all of the owners, in
alphabetical order by last name. For future reference (and in case anyone asks), this type of join is
considered to be in the category of
inner joins.