www.mimer.com/developer

Eliminating Duplicate Values

---

The simple SELECT statement retrieves all rows which fulfill the selection conditions. The result table does not have a primary key, and may contain duplicate values.

For example:

 SELECT RESERVATION, CHARGE_CODE
 FROM BILL;

Result:

RESERVATION	CHARGE_CODE
1347	100
1347	120
1347	210
1347	700
1347	120
1348	700
1348	700
1348	200
1348	230
...	...

By adding the keyword DISTINCT before the column list you can eliminate all duplicate rows from the result table. The keyword DISTINCT may only be used once in a simple SELECT statement.

For example:

 SELECT DISTINCT RESERVATION, CHARGE_CODE
 FROM BILL;

Result:

RESERVATION	CHARGE_CODE
1347	100
1347	120
1347	210
1347	700
1348	700
1348	200
1348	230
...	...

DISTINCT also eliminates duplicate rows containing NULL values, although technically NULL is not regarded as equal to NULL, see Handling NULL Values.

If the selected columns include the whole primary key in the source table, the keyword DISTINCT is unnecessary, since all rows in the result table will be unique. Remember however that a view may contain duplicate rows, so that selecting all columns does not always guarantee that the result does not contain duplicate rows.

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se