Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SELECT UNION as DISTINCT

How do I perform a DISTINCT operation on a single column after a UNION is performed?

T1
--
ID Value 
1  1
2  2
3  3

T2
--
ID Value
1  2
4  4
5  5


I am trying to return the table:

ID Value
1  1
2  2
3  3
4  4
5  5

I tried:

SELECT DISTINCT ID, Value 
FROM (SELECT*FROM T1 UNION SELECT*FROM T2) AS T3

This does not seem to work.

like image 356
user1124535 Avatar asked Jan 09 '12 00:01

user1124535


People also ask

Does UNION SELECT distinct?

UNION DISTINCT operator is used for combining DISTINCT result sets from more than one SELECT statement into one result set. Any duplicate rows from the results of the SELECT statements are eliminated so, here all the entries in the result set are distinct.

What is difference between UNION and distinct?

Answers. Select Distinct is used to select distinct Combination of Cols , normally used with a JOIN. UNION just Joins and gets distinct rows from two sets which have eaqul number of columns.

How do I remove duplicates in UNION?

The SQL UNION ALL operator does not remove duplicates. If you wish to remove duplicates, try using the UNION operator.

What is UNION distinct BigQuery?

The UNION DISTINCT unites and discards duplicate items from the result sets of two or more input queries. Note: The UNION DISTINCT in BigQuery is equivalent to UNION in SQL.

What is Union distinct in SQL Server?

UNION DISTINCT is the default mode, and it will eliminate duplicate records from the second query. That’s similar to the logic of SELECT DISTINCT or FOR ALL ENTRIES.

How do I Union multiple select statements?

The UNION operator is used to combine the result-set of two or more SELECT statements. Every SELECT statement within UNION must have the same number of columns The columns in every SELECT statement must also be in the same order The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

What is the difference between Union all and Union distinct?

UNION ALL is faster than UNION, but it does not remove duplicates. Including DISTINCT in a UNION query does not add anything. Show activity on this post. Based on the query you have provided I am assuming the DistinctValue column does not initially give you a set of distinct values so one approach you may try is the following:

How do you use Union in SQL?

The UNION operator is used to combine the result-set of two or more SELECT statements. Every SELECT statement within UNION must have the same number of columns The columns in every SELECT statement must also be in the same order The UNION operator selects only distinct values by default.


2 Answers

Why are you using a sub-query? This will work:

SELECT * FROM T1
UNION
SELECT * FROM T2

UNION removes duplicates. (UNION ALL does not)

like image 98
Bohemian Avatar answered Oct 18 '22 19:10

Bohemian


As far as I can say, there's no "one-column distinct": 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.

Try using GROUP BY to ensure ID is not repeated, and any aggregate (here MIN, as in your example it was the minimum that survived) to select a particular value of Value:

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.

like image 30
alf Avatar answered Oct 18 '22 20:10

alf