DISTINCTROW

[Paul fpvt2]

What is the difference between DISTINCTROW and DISTINCT.
I read the following:

1. If every table appearing in the from clause of a query has at least one
column in the select list, DISTINCTROW does nothing, and so can be ommited.
In 99% of queries this it the case.
Question: Is the following a correct example of the above statement, where I
can ommit the DISTINCTROW ?
select DISTINCTROW tblA.colA, tblB.colB, tblC.colC FROM (tblA LEFT JOIN tblB
ON tblA.id = tblB.id) LEFT JOIN tblC ON tblA.id = tblC.id where tblA.colAA =
123


2. If a query outputs (includes in its select list) the primary key for each
table having a field appear in the select list, DISTINCTROW is equivilent to
DISTINCT.
Question: what does the above statement mean ? I use DISTINCT when I want
the result to only show those rows that have a DISTINCT value of a particular
column/field.

3. In short DISTINCTROW appears in many access queries where it does nothing,
and can usually be omitted, or replaced with DISTINCT.
Question: Does it mean that it will be safe if I convert DISTINCTROW to
DISTINCT, but, if the query is like the query in #1 above, I can just ommit
the DISTINCTROW, and does not have to replace it with DISTINCT, right ?

Thanks a lot.

 

[Roger Carlson]

DISTINCTROW will remove duplicates across the entire record. This means
that it will ONLY remove records that are exact duplicates in every field.
If you have a primary key in a record (ALL of my tables do) then DISTINCTROW
is meaningless. You will ALWAYS have a distinct row in that case.

on the other hand

DISTINCT will remove duplicate records from a query ONLY on the fields
listed in the Field List.

Example: Assume the following table:
tblCustomers:
CustomerID LName FName
1 Carlson Roger
2 Snead Sam
3 Carlson Roger
4 Snead Pam

The following query:
Select DISTINCT Lname, FName From tblCustomers
would produce:
Carlson Roger
Snead Sam
Snead Pam
because those are distinct record for the fields in the field list.

However:
Select DISTINCTROW Lname, FName From tblCustomers
would produce:
Carlson Roger
Snead Sam
Carlson Roger
Snead Pam
because these records are distinct across ALL rows (even those not
displayed)

Really, DISTINCTROW is mostly useless unless you have tables without a
primary key or any unique indexes. I can't remembered the last time I used
it.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Paul fpvt2]

Thanks.
In my case, the sql statement is querying 2 tables and 1 stored query.
The 2 tables do NOT have primary keys.
tblA has no primary key but has indexes on 2 columns/fields (tblAcolA and
tblAcolB), but these are not unique indexes.
tblB does not have primary key and does not have index.

queryA is a query for tblC and tblD.
tblC has a primary key and index.
tblD has NO primary key but has indexes on 7 out of 9 columns/fields.

The sql statement is something like:
SELECT DISTINCTROW tblA.tblAcolC, tblB.tblBcolA, queryA.queryAcolA
FROM (queryA LEFT JOIN tblB ON queryA.queryAcolA = tblB.tblBcolB) LEFT JOIN
tblA ON queryA.queryAcolB = tblA.tblAcolD

We are moving from Access to SQL Server, and SQL Server does not have
DISTINCTROW.
In the above sql statement, can I ommit the DISTINCTROW, or should I replace
it with DISTINCT ?

Thanks.

 

[Roger Carlson]

I believe you may omit the DISTINCTROW.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Michel Walsh]

Hi,


DISTINCTROW can be seen to work like this:

1- temporary add the primary key field of all the tables mentioned in
the SELECT clause (that may be not necessary those mentioned in the FROM
clause).
2- make the SELECT DISTINCT on those initial SELECTed fields, augmented
of the primary key mentioned in 1-
3- remove the primary key field mentioned in 1.



If a table has no primary key, supply a temporary one (for the query
solution).

Among other effects, it keeps tracks of the initial record supplying the
SELECTed values, which may be useful for UPDATE or DELETE on a join.

Hoping it may help,
Vanderghast, Access MVP

 

[Paul fpvt2]

Thanks.

If a table has no primary key, supply a temporary one (for the query solution).

Do you mean, if the table has no primary key, I need to supply it with a
temporary one, or does DISTINCTROW supply the temporary primary key ?

Thanks.

 

[Michel Walsh]

Hi,

The result is "as if" DISTINCTROW would have supplied one. If you supply
it, and SELECT it, for all the tables involved in the SELECT, you could have
use DISTINCT to get the same result, but remember that with DISTINCTROW, the
query does not loose its updateability status, while it does, with DISTINCT.




Hoping it may help,
Vanderghast, Access MVP

 

[Paul fpvt2]

Thanks.

with DISTINCTROW, the query does not loose its updateability status, while it does, > with DISTINCT.

What did you mean by it does not loose its updateability status ?

Thanks.

 

[Michel Walsh]

Hi,


A query with DISTINCT won't be updateable.

A query that was previously updateable, add DISTINCTROW to it, the
result is still an updateable query. Sure, if the query was not updateable
in the first place, adding DISTICTROW won't make it updateable ...

I assume SELECT query in what preceded. You NEED the use of
DISTINCTROW to keep a DELETE query updateable (able to delete rows would be
more appropriate, in that case) over a JOIN.


Hoping it may help,
Vanderghast, Access MVP

 

[Paul fpvt2]

Thanks for your help.

 

[Paul fpvt2]

Thanks for your help.

I believe you may omit the DISTINCTROW.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L