DISTINCTROW
predicate is MS Access database engine specific. As MSDN documentation states, there's small difference between
DISTINCT
and
DISTINCTROW
:
DISTINCT
- Omits records that contain duplicate data in the selected fields.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.
See:
ALL, DISTINCT, DISTINCTROW, TOP Predicates (Microsoft Access SQL) [Access 2007 Developer Reference][
^]
So, if you want to get data in the way you can fetch them with
DISTINCTROW
predicate, you have to create subquery. Depending on situation, you may need to use one of the following predicates/methods:
TOP
,
ROW_NUMBER()
or
LEFT|RIGHT JOIN
.
For further details, please see:
Visual Representation of SQL Joins[
^]
Ranking Functions (Transact-SQL) | Microsoft Docs[
^]