ALL, DISTINCT, DISTINCTROW, TOP Predicates

Description

Specifies records selected with SQL queries.

Syntax

SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]] FROM table

A SELECT statement containing these predicates has the following parts

Part	Description

ALL	Assumed if you don't include one of the predicates. The Microsoft Jet database engine selects all of the records that meet the conditions in the SQL statement. The following two examples are equivalent and return all records from the Employees table: SELECT ALL * FROM Employees ORDER BY EmployeeID; SELECT * FROM Employees ORDER BY EmployeeID;
DISTINCT	Omits records that contain duplicate data in the selected fields. To be included in the results of the query, the values for each field listed in the SELECT statement must be unique. For example, several employees listed in an Employees table may have the same last name. If two records contain Smith in the LastName field, the following SQL statement returns only one record that contains Smith: SELECT DISTINCT LastName FROM Employees; If you omit DISTINCT, this query returns both Smith records. If the SELECT clause contains more than one field, the combination of values from all fields must be unique for a given record to be included in the results. The output of a query that uses DISTINCT isn't updatable and doesn't reflect subsequent changes made by other users.

(continued)

Part	Description

DISTINCTROW	Omits data based on entire duplicate records, not just duplicate fields. For example, you could create a query that joins the Customers and Orders tables on the CustomerID field. The Customers table contains no duplicate CustomerID fields, but the Orders table does because each customer can have many orders. The following SQL statement shows how you can use DISTINCTROW to produce a list of companies that have at least one order but without any details about those orders: SELECT DISTINCTROW CompanyName FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY CompanyName; If you omit DISTINCTROW, this query produces multiple rows for each company that has more than one order. DISTINCTROW has an effect only when you select fields from some, but not all, of the tables used in the query. DISTINCTROW is ignored if your query includes only one table, or if you output fields from all tables.
TOP n [PERCENT]	Returns a certain number of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose you want the names of the top 25 students from the class of 1994: SELECT TOP 25 FirstName, LastName FROM Students WHERE GraduationYear = 1994 ORDER BY GradePointAverage DESC; If you don't include the ORDER BY clause, the query will return an arbitrary set of 25 records from the Students table that satisfy the WHERE clause. The TOP predicate doesn't choose between equal values. In the preceding example, if the twenty-fifth and twenty-sixth highest grade point averages are the same, the query will return 26 records. You can also use the PERCENT reserved word to return a certain percentage of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose that, instead of the top 25 students, you want the bottom 10 percent of the class: SELECT TOP 10 PERCENT FirstName, LastName FROM Students WHERE GraduationYear = 1994 ORDER BY GradePointAverage ASC; The ASC predicate specifies a return of bottom values. The value that follows TOP must be an unsigned Integer. TOP doesn't affect whether or not the query is updatable.
table	The name of the table from which records are retrieved.

See Also

FROM clause, SELECT statement.

Specifics (Microsoft Access)

In Microsoft Access, the use of the DISTINCT reserved word is equivalent to setting the UniqueValues property to Yes on the query's property sheet in query Design view.

The use of the DISTINCTROW reserved word is equivalent to setting the UniqueRecords property to Yes (the default value) in the query's property sheet in query Design view.

The use of the TOP reserved word is equivalent to setting the TopValues property on the query's property sheet in query Design view, or entering a value in the Top Values box on the Query Design toolbar.

The use of the PERCENT reserved word is equivalent to using the percent sign (%) with the TopValues property on the query's property sheet, or in the Top Values box.

Example

This example creates a query that joins the Customers and Orders tables on the CustomerID field. The Customers table contains no duplicate CustomerID fields, but the Orders table does because each customer can have many orders. Using DISTINCTROW produces a list of companies that have at least one order but without any details about those orders.

Sub AllDistinctX()

    Dim dbs As Database, rst As Recordset

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")

    ' Join the Customers and Orders tables on the
    ' CustomerID field. Select a list of companies
    ' that have at least one order.
    Set rst = dbs.OpenRecordset("SELECT DISTINCTROW " _
        & "CompanyName FROM Customers " _
        & "INNER JOIN Orders " _
        & "ON Customers.CustomerID = " _
        & "Orders.CustomerID " _
        & "ORDER BY CompanyName;")

    ' Populate the Recordset.
    rst.MoveLast
    
    ' Call EnumFields to print the contents of the
    ' Recordset. Pass the Recordset object and desired
    ' field width.
    EnumFields rst, 25

    dbs.Close

End Sub

Example (Microsoft Access)

To try the following examples in Microsoft Access, create two new tables like those shown below. Create a new query in the Northwind sample database and close the Show Table dialog box without specifying a table or query. Switch to SQL view, paste an individual example into the SQL window, and run the query.

Clients Table

FirstName	ClientID

Bob	1
Adam	2
Beverly	3
Bob	4

Invoices Table

ClientID	InvoiceID

1	1
1	2
2	3
2	4
2	5
4	6
4	7

The following example returns all of the records returned by an inner join on the two tables. The result set is updatable.

SELECT ALL FirstName FROM Clients INNER JOIN Invoices
ON Clients.ClientID = Invoices.ClientID;

Result	Updatable

Bob Bob Adam Adam Adam Bob Bob	Yes

The next example selects only records with unique values returned by an inner join on the two tables. The result set isn't updatable.

SELECT DISTINCT FirstName FROM Clients INNER JOIN Invoices
ON Clients.ClientID = Invoices.ClientID;

Result	Updatable

Bob Adam	No

The following example selects only unique records returned by an inner join on the two tables. The result set is updatable.

SELECT DISTINCTROW FirstName FROM Clients INNER JOIN Invoices
ON Clients.ClientID = Invoices.ClientID;

Result	Updatable

Bob Adam Bob	Yes

The next example selects the first five records returned by an inner join on the two tables. The result set is updatable.

SELECT TOP 5 FirstName FROM Clients INNER JOIN Invoices
ON Clients.ClientID = Invoices.ClientID
ORDER BY Invoices.InvoiceID;

Result	Updatable

Bob Bob Adam Adam Adam	Yes