Ok...I spoke with one of our Oracle DBAs. The problem lies in that if you have a set like this
1, 2, A, B, C 1, 2, D, E, F 2, 2, A, B, C 2, 3, D, E, F and did your select statement which one of the rows would you want. What I mean is this... You have these two rows both with your 1,2 but which of the (A,B,C or D,E,F) rows would you want returned with the 1,2. If you're using CF I would just return the recordset straight up and manipulate it with code. 1, 2, A, B, C 1, 2, D, E, F -----Original Message----- From: Steven Dworman [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 13, 2001 9:42 AM To: SQL Subject: RE: Selecting DISTINCT rows 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. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
