Publishers of technology books, eBooks, and videos for creative people

Home > Articles

  • Print
  • + Share This
  • 💬 Discuss
This chapter is from the book

Eliminating Duplicate Rows with DISTINCT

Columns often contain duplicate values, and it’s common to want a result that lists each duplicate only once. If I type Listing 4.6 to list the states where the authors live, the result, Figure 4.6, contains unneeded duplicates. The DISTINCT keyword eliminates duplicate rows from a result. Note that the columns of a DISTINCT result form a candidate key (unless they contain nulls).

Listing 4.6. List the states in which the authors live. See Figure 4.6 for the result.

SELECT state
  FROM authors;

Figure 4.6 Result of Listing 4.6. This result contains unneeded duplicates of CA and NY.

state
-----
NY
CO
CA
CA
NY
CA
FL

To eliminate duplicate rows:

  • Type:

    SELECT DISTINCT columns
      FROM table;

    columns is one or more comma-separated column names, and table is the name of the table that contains columns (Listing 4.7 and Figure 4.7).

    Listing 4.7. List the distinct states in which the authors live. The keyword DISTINCT eliminates duplicate rows in the result. See Figure 4.7 for the result.

    SELECT DISTINCT state
      FROM authors;

    Figure 4.7 Result of Listing 4.7. This result has no CA or NY duplicates.

    state
    -----
    NY
    CO
    CA
    FL
  • + Share This
  • 🔖 Save To Your Account

Discussions

comments powered by Disqus