InterBase Quick Start: Part IV - Removing Duplicate Rows with DISTINCT

From InterBase
Jump to: navigation, search

Go Up to InterBase Quick Start: Part IV - Retrieving Data

Columns often contain duplicate entries. The DISTINCT keyword allows you to remove duplicate values from the result. It enables you to retrieve only distinct (different) values.

Image 025.jpgSelecting One of Each

  1. If you want to retrieve all the job codes from the Job Table, you can do that using the following query:
    SELECT job_code
    FROM   Job
    

    As you can see, the result of this query contains many values, some of them duplicates. To remove duplicate values from the result, you have to use the DISTINCT keyword.

  2. Modify the previous query to include the DISTINCT keyword:
    SELECT DISTINCT job_code
    FROM   Job
    

    This produces the desired result, each job code is listed only once.

  3. Now enter a query that specifies two columns using the DISTINCT keyword:
    SELECT DISTINCT job_code,
                    job_grade
    FROM   Job
    

    DISTINCT applies to all columns thay you specify in a SELECT statement. In this case, the result shows duplicate job codes. That is because DISTINCT treats the job_code and job_grade together, so the combination of values in the result is distinct (no duplicates). You can see the expected result of this query in the image below:

    TutorialDistinct.png

Advance To: