InterBase Quick Start: Part IV - Removing Duplicate Rows with DISTINCT
Go Up to InterBase Quick Start: Part IV - Retrieving Data
Columns often contain duplicate entries (assuming that they do not have PRIMARY KEY or UNIQUE constraints on them). Sometimes you want to see only one instance of each value in a column. The DISTINCT keyword gives you exactly that.
Selecting One of Each
1. Suppose you want to retrieve a list of all the valid job codes in the TUTORIAL database. Begin by entering this query:
SELECT job_code FROM Job
- As you can see, the results of this query are rather long, and some job codes are repeated a number of times. What you really want is a list of job codes where each value returned is distinct from the others. To eliminate duplicate values, use the DISTINCT keyword.
2. Re-enter the previous query with the DISTINCT keyword:
SELECT DISTINCT job_code FROM Job
- This produces the desired results: each job code is listed only once in the results.
3. What happens if you specify another column when using DISTINCT? Enter the following SELECT statement:
SELECT DISTINCT job_code, job_grade FROM Job
- This query returns:
- DISTINCT applies to all columns listed in a SELECT statement. In this case, duplicate job codes are retrieved. However, DISTINCT treats the job code and job grade together, so the combination of values is distinct.