Predicate

Use this predicate to restrict the data returned by the query, e.g. to avoid returning duplicate rows.

Syntax: 

 ALL | DISTINCT | DISTINCTROW | TOP nn[%]

Example usage: 

 SELECT DISTINCT Name
 FROM Contact

The predicate alternatives have the following meanings:
ALL This is assumed if no predicate is given. All rows meeting the other conditions in the SELECT statement are returned
DISTINCT Duplicate values in the returned table are omitted.
DISTINCTROW Duplicate values in the selected source table rows are omitted
TOP The top nn data rows are returned. The top nn% rows are returned in the percent sign is included


NOTES:

  1. DISTINCT differs from DISTINCTROW. This is best illustrated in an example:

    Assuming the following data exists in table Toon in data source MyData:
    FirstName LastName
    Wilma Flintstone
    Fred Flintstone

    Then 

     SELECT DISTINCTROW LastName FROM Toon

    results in
    LastName
    Flintstone
    Flintstone

    because the two rows are distinct in the source table. However, 

     SELECT DISTINCT LastName FROM Toon

    results in
    LastName
    Flintstone

    because the two rows are identical in the returned table.

  2. TOP returns the nn or nn% rows at the top of the table, as opposed to the top nn values.

[Return to SQL Syntax]