Use these predicates to specify which records a SQL query selects.
Syntax
SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]]
FROM table
A SELECT statement that contains these predicates has the following parts:
| Part | Description |
|---|---|
ALL |
Access assumes ALL if you don't include another predicate. The Access database engine selects all records that meet the conditions in the SQL statement. These examples are equivalent and return all records from the Employees table: SELECT ALL * FROM Employees ORDER BY EmployeeID; and SELECT * FROM Employees ORDER BY EmployeeID; |
DISTINCT |
Omits records that contain duplicate data in the selected fields. To include a record in the results, the values for each field in the SELECT statement must be unique. For example, several employees in an Employees table might have the same last name. If two records contain Smith in the LastName field, SELECT DISTINCT LastName FROM Employees; returns only one record that contains Smith. If you omit DISTINCT, the 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 record to appear in the results. The output of a query that uses DISTINCT isn't updateable and doesn't reflect later changes made by other users. |
DISTINCTROW |
Omits data based on entire duplicate records, not just duplicate fields. For example, you can create a query that joins the Customers and Orders tables on the CustomerID field. The Customers table has no duplicate CustomerID fields, but the Orders table does because each customer can have many orders. SELECT DISTINCTROW CompanyName FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY CompanyName; produces a list of companies that have at least one order, but it doesn't include details about those orders. If you omit DISTINCTROW, the 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. Access ignores DISTINCTROW if your query includes only one table, or if you output fields from all tables. |
TOP n [PERCENT] |
Returns a specific number of records that fall at the top or bottom of a range that an ORDER BY clause specifies. Suppose you want the names of the top 25 students from the class of 2003: SELECT TOP 25 FirstName, LastName FROM Students WHERE GraduationYear = 2003 ORDER BY GradePointAverage DESC; If you don't include the ORDER BY clause, the query returns 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 returns 26 records. You can also use the PERCENT reserved word to return a percentage of records that fall at the top or bottom of a range that an ORDER BY clause specifies. For example, if you want the bottom 10 percent of the class, use SELECT TOP 10 PERCENT FirstName, LastName FROM Students WHERE GraduationYear = 2003 ORDER BY GradePointAverage ASC; The ASC predicate returns bottom values. The value that follows TOP must be an unsigned integer. TOP doesn't affect whether the query is updateable. |
table |
The name of the table from which Access retrieves records. |