[AccessD] Select question

>From Access 2000 help files

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 is not updatable and does not
reflect subsequent changes made by other users.
 
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.
 

HTH

******************************************************************
*Patricia O'Connor
*Associate Computer Programmer Analyst
*OTDA - BDMA
*(W) mailto:Patricia.O'Connor at dfa.state.ny.us
*(w) mailto:aa1160 at dfa.state.ny.us
******************************************************************
 

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Eget
> Sent: Thursday, August 19, 2004 05:26 AM
> To: AccessD at databaseadvisors.com
> Subject: [AccessD] Select question
> 
> What is the difference between select distinct and select 
> distinctrow. When would I use one instead of the other. I 
> have looked and cannot find the answer Thanks John
> --
> _______________________________________________