SQL Predicate Examples
SQL Predicates are simple but important to understand for full
use of the query programming language.
There are five predicate functions:
-
Select All
-
Select Distinct
-
Select Distinctrow
-
Select Top
-
Select Top Percent
The ALL command is the default when you use a select
statement:
Select * from Employees
is equivalent to entering
Select All * from Employees
The Distinct command is often confused with the distinctrow keyword. Here is an example of the difference:
Select Distinctrow Last_Name from Employees
This query won't necessarily retrieve a distinct list of employee last names. If there are duplicates last
names and ANY other field has different data between the two duplicate name records then you will get
both records even though they have the same last name. Whereas:
Select Distinct Last_Name from Employees
Use the distinct directive to query unique records.
Distinct will retrieve a unique list of employee last names because the
Distinct command only looks at the fields you are returning in the query.
I have never had a reason to use the Distinctrow function because our tables
never have duplicate rows.
Select TOP (aka Top Values/Top Values) is explained in an example page:
Select
Top 10 Records, but we will review it here combined with the PERCENT
option:
Select Top 10 Last_Name from Employees
Order By Age desc
Using the Top 10 example tells the query engine to return 10
records, in this case it will be the 10 oldest employees. Here is the
percent option:
Select Top 10 Percent Last_Name from Employees Order By Age
desc
In this case, if you had 1000 employees in the Employee table
you would retrieve 100 records containing a list of the 100 oldest employees.
Now you know all about the use of predicates in the Microsoft Access
programming language.
