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. > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
