By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,480 Members | 763 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,480 IT Pros & Developers. It's quick & easy.

Distinct vs DistinctRow?

P: n/a
Can someone explain the difference between these 2 queries?

"Select Distinct id, account, lastname, firstname from table1"
and
"Select DistinctRow id, account, lastname, firstname from table1"

Thanks,
Fred Zuckerman

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
From the help file:

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.
Basically, the difference is whether the entire record is distinct
(including fields not returned by the query) or if just the output of the
query has no duplicates.
--
Wayne Morgan
MS Access MVP
"Fred Zuckerman" <Zu********@sbcglobal.net> wrote in message
news:GQ****************@newssvr21.news.prodigy.com ...
Can someone explain the difference between these 2 queries?

"Select Distinct id, account, lastname, firstname from table1"
and
"Select DistinctRow id, account, lastname, firstname from table1"

Thanks,
Fred Zuckerman

Nov 13 '05 #2

P: n/a
> "Fred Zuckerman" <Zu********@sbcglobal.net> wrote in message
Can someone explain the difference between these 2 queries?
"Select Distinct id, account, lastname, firstname from table1"
and
"Select DistinctRow id, account, lastname, firstname from table1"
Thanks,
Fred Zuckerman

"Wayne Morgan" <co***************************@hotmail.com> wrote in message> Basically, the difference is whether the entire record is distinct
(including fields not returned by the query) or if just the output of the
query has no duplicates.
--
Wayne Morgan
MS Access MVP


Ok. I think I got it. Great.
Thanks,
Fred
Nov 13 '05 #3

P: n/a
"Fred Zuckerman" <Zu********@sbcglobal.net> wrote in
news:Yo*****************@newssvr21.news.prodigy.co m:
"Fred Zuckerman" <Zu********@sbcglobal.net> wrote in message
> Can someone explain the difference between these 2 queries?
> "Select Distinct id, account, lastname, firstname from table1"
> and
> "Select DistinctRow id, account, lastname, firstname from
> table1" Thanks,
> Fred Zuckerman


"Wayne Morgan" <co***************************@hotmail.com> wrote
in message>
Basically, the difference is whether the entire record is
distinct (including fields not returned by the query) or if just
the output of the query has no duplicates.


Ok. I think I got it. Great.


A couple of other things:

A DISTINCT query is never editable.

A DISTINCTROW query will often be editable when a plain SELECT is
not.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #4

P: n/a
On Fri, 24 Jun 2005 21:59:24 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
"Fred Zuckerman" <Zu********@sbcglobal.net> wrote in
news:Yo*****************@newssvr21.news.prodigy.c om:
"Fred Zuckerman" <Zu********@sbcglobal.net> wrote in message
> Can someone explain the difference between these 2 queries?
> "Select Distinct id, account, lastname, firstname from table1"
> and
> "Select DistinctRow id, account, lastname, firstname from
> table1" Thanks,
> Fred Zuckerman


"Wayne Morgan" <co***************************@hotmail.com> wrote
in message>
Basically, the difference is whether the entire record is
distinct (including fields not returned by the query) or if just
the output of the query has no duplicates.


Ok. I think I got it. Great.


A couple of other things:

A DISTINCT query is never editable.

A DISTINCTROW query will often be editable when a plain SELECT is
not.


Funny enough, in Access 97, DISTINCTROW is sometimes required to make a query
editable, while in Access 2000, the same query will only be editable if
DISTINCTROW is ommitted. In Access 2000 and above, I have yet to find any
cases where DISTINCTROW was of any use.
Nov 13 '05 #5

P: n/a

This is a fairly brilliant demonstration of your copy and paste abilities,
Wayne. Wear your MVP badge with pride!

"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:6f****************@newssvr30.news.prodigy.com ...
From the help file:

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.
Basically, the difference is whether the entire record is distinct
(including fields not returned by the query) or if just the output of the
query has no duplicates.
--
Wayne Morgan
MS Access MVP
"Fred Zuckerman" <Zu********@sbcglobal.net> wrote in message
news:GQ****************@newssvr21.news.prodigy.com ...
Can someone explain the difference between these 2 queries?

"Select Distinct id, account, lastname, firstname from table1"
and
"Select DistinctRow id, account, lastname, firstname from table1"

Thanks,
Fred Zuckerman


Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.