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
