How do I perform a DISTINCT operation on a single column after a UNION is performed?
SELECT DISTINCT ID, Value
FROM (SELECT*FROM T1 UNION SELECT*FROM T2) AS T3
As far as I can say, there's no "one-column
distinct is always applied to a whole record (unless used within an aggregate like
count(distinct name)). The reason for this is, SQL cannot guess which values of
Value to leave for you—and which to drop. That's something you need to define by yourself.
GROUP BY to ensure
ID is not repeated, and any aggregate (here
MIN, as in your example it was the minimum that is survived) to select a particular value of
SELECT ID, min(Value) FROM (SELECT * FROM T1 UNION ALL SELECT * FROM T2) AS T3 GROUP BY ID
Should be exactly what you need. That is, it's not the same query, and there's no
distinct—but it's a query which would return what's shown in the example.