List of distinct values

We may have a need to pull out all the different values in a field display them in a list. If we wanted to get a list of all the countries associated with the students in our table we could try to write a query:

SELECT country
FROM student;

What we would get back is a list of all the countries of each record. If we had two students from the same country we would see that country listed twice in our list.

In order to show the results without repeating values we can use the keyword DISTINCT. This will strip away duplicate values from our results. To use the command we would write:

SELECT DISTINCT country
FROM student;

We can also use the ORDER BY to sort the results. So now a better query may be:

SELECT DISTINCT country
FROM student
ORDER BY country;

Now lets say we want a list of all the countries in which the students in our table are over the age of 40. We could write this query as:

SELECT DISTINCT country
FROM student
WHERE age > 40
ORDER BY country;

Although we are not displaying ages, based on the criteria a list of countries of students aged 41 and higher would be returned.

Lesson Activities

  1. Use a basic SELECT query to see the city field of the student table.
    Click to see code
    SELECT city
    FROM student;
  2. Now let’s see the cities again, but this time get a DISTINCT list of the cities.
    Click to see code
    SELECT DISTINCT city
    FROM student;
  3. Lastly, organize the distinct list of cites in descending order.
    Click to see code
    SELECT DISTINCT city
    FROM student
    ORDER BY city DESC;

Questions