Re: DISTINCTROW

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Paul fpvt2 (Paulfpvt2_at_discussions.microsoft.com)
Date: 09/28/04 

Date: Tue, 28 Sep 2004 12:03:04 -0700

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" wrote:

> 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" <Paulfpvt2@discussions.microsoft.com> wrote in message
> news:90B27972-B97F-4F97-9BEA-A782CAEEBD59@microsoft.com...
> > 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.
> >
> >
> >
>
>
>

Relevant Pages

  • Re: Create an MS Access Table in VB6
    ... You can run a simple DDL query to ... MyID AutoIncrement CONSTRAINT MyIdConstraint PRIMARY KEY, ... cn.Execute strsql ' the above sql statement ... create an AutoNumbering Index in my table as a Primary Key. ...
    (microsoft.public.vb.general.discussion)
  • referencing the outermost table from an inner view which is inside a subselect
    ... create table alm ( ... all_cod varchar2primary key, ... This mean that I have to write a SQL statement that cycles through all ... first query passing to it the values retrieved from all_cod_list instead ...
    (comp.databases.oracle.server)
  • Re: subform records
    ... open the Immediate Window. ... Copy the SQL statement there, and paste it into SQL View in a new query. ... tblSet, with SetID primary key, and OrderID foreign key. ... Dim strSql as String ...
    (microsoft.public.access.formscoding)
  • Re: subform records
    ... open the Immediate Window. ... Copy the SQL statement there, and paste it into SQL View in a new query. ... Allen Browne - Microsoft MVP. ... tblSet, with SetID primary key, and OrderID foreign key. ...
    (microsoft.public.access.formscoding)
  • Re: Slow GROUP BY
    ... ' 'create primary key ... PunchList INNER JOIN Lot ON PunchList.LotId = Lot.Id INNER JOIN PunchItem ON ... the exact schema of the parent and child table ... your exact SQL statement that includes the GROUP BY ...
    (microsoft.public.dotnet.framework.compactframework)