Mimer SQL User's Manual TOC PREV NEXT INDEX

Mimer Developer Site

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
Mimer SQL User's Manual TOC PREV NEXT INDEX