Yes, the concept of RowID was exactly what I was thinking of.
(Unfortunately, I'm using SQL Server.)
In response to Nelson's comment,
It doesn't matter if I get
Dog, Mammal, Poodle
OR
Dog, Mammal, Golden Retriever
as long as I get one of them.
The reason being that the two (or three or four) rows are related and I'll
be able to derive the information for the second one from the information
from the first one (irrespective of which row the DISTINCT query gave me).
The idea of SELECT DISTINCT
colA, colB, min(colC), min(colD), min(colE)
is interesting. It might be useful since colA is my primary key.
If I did SELECT DISTINCT min(colA), colB, colC
would I then be able to do something like
SELECT *
FROM table
WHERE EXISTS subquery
I tried this, but it threw two errors.
It's sooo frustrating because the SELECT DISTINCT returns the rows I want.
In any event, I changed my db. The person who enters the data now has to
flag the records that shouldn't be pulled.
Thanks Dean and Steve for your responses.
Matt
> -----Original Message-----
> From: Dean H. Saxe [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, December 13, 2001 9:50 AM
> To: SQL
> Subject: RE: Selecting DISTINCT rows
>
> You will never get a DISTINCT row with only two columns if you join the
> base table to itself, in fact, you will generate the same as a SELECT *
> FROM table; query. That being said, in Oracle you could use RowID to get
> a
> pointer to a specific row with two distinct columns and join that back to
> the base table to get a single row per RowID. Similar methods would work
> in SQL Server, but they are ugly.
>
> -dhs
>
> At 09:42 AM 12/13/01 -0500, you wrote:
> >I never tested this code. I just suggested trying something like it.
> >Granted I didn't explicity say it. On top of that I specifically said
> union
> >and not union all because I know a union all will not return distinct
> rows.
> >
> >Actually your best bet would be to create a view by joining the table on
> >itself. I don't have the code worked out in front of me, but we'll see
> what
> >I come up with today.
> >
> >-----Original Message-----
> >From: Dean H. Saxe [mailto:[EMAIL PROTECTED]]
> >Sent: Thursday, December 13, 2001 8:46 AM
> >To: SQL
> >Subject: Re: Selecting DISTINCT rows
> >
> >
> >Except that doesn't give anything like what the poster wants.
> >
> >If you have five columns, colA -> colE and the following data:
> >
> >1, 2, 3, 4, 5
> >1, 2, 3, 4, 6
> >2, 2, 3, 4, 5
> >2, 3, 3, 4, 6
> >
> >Your queries return:
> >
> >1, 2, NULL, NULL, NULL
> >1, 2, NULL, NULL, NULL
> >2, 2, NULL, NULL, NULL
> >2, 3, NULL, NULL, NULL
> >NULL, NULL, 3, 4, 5
> >NULL, NULL, 3, 4, 5
> >NULL, NULL, 3, 4, 5
> >NULL, NULL, 3, 4, 6
> >
> >Your statement regarding UNION always retrieving distinct rows is
> >misleading. If two rows are identical in the two parts of the query,
> they
> >will be returned as a single row. However, if you specify UNION ALL,
> both
> >rows will be returned.
> >
> >That having been said, the original poster *could* do something like the
> >following, though the results may not be what is desired:
> >
> >SELECT DISTINCT
> > colA, colB, min(colC), min(colD), min(colE)
> >FROM
> > table
> >GROUP BY
> > colA, colB;
> >
> >Which would give distinct rows based on colA and colB, however, the
> values
> >of min(colC), min(colD) and min(colE) may be from different rows in the
> >table producing misleading results. For example, if the table contains
> the
> >following rows:
> >
> >1, 2, 3, 5, 5
> >1, 2, 4, 4, 1
> >1, 2, 5, 3, 2
> >2, 3, 3, 4, 6
> >2, 3, 3, 4, 6
> >
> >The previous query will return:
> >
> >1, 2, 3, 3, 1
> >2, 3, 3, 4, 6
> >
> >In the first returned row, the first two columns are DISTINCT compared to
> >any other row. The last three columns, however, are pulled from rows 1,
> 3
> >and 2, respectively. This is because of the aggregate function min().
> So,
> >the net result is that the first returned row is not representative of
> any
> >rows in the database, its an amalgamation. The second returned row is
> >identical to a row in the database, however, this is an artifact of the
> >data because rows 4 and 5 in the original table are identical.
> >
> >Make sense?
> >
> >-dhs
> >
> >
> >
> >
> >At 06:38 PM 12/12/01 -0500, you wrote:
> > >try a union on itself
> > >
> > >select ColA,ColB, Null as ColC, Null as ColD, Null as ColE
> > >from table_name
> > >
> > >union
> > >
> > >select Null as ColA, Null as ColB,ColC, ColD, ColE
> > >from table_name
> > >
> > >union queries will automatically give you distinct records.
> >
> >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
