Adding DISTINCT to a UNION query

mysql sql union distinct union-all
cdub picture cdub · Jun 2, 2011 · Viewed 21k times · Source

How do I get distinct title.id's from this:

 SELECT Title.id, Title.title FROM titles as Title HAVING points > 0 
 UNION ALL 
 SELECT Title.id, Title.title FROM titles as Title HAVING points > 1

There is more to the query but this should be enough to go on.

Answer

Alin Purcaru picture Alin Purcaru · Jun 2, 2011

Just remove the ALL. Some flavors allow adding DISTINCT instead of ALL to be more explicit, but that's redundant having that the default is always to filter our duplicates.

MySQL - http://dev.mysql.com/doc/refman/5.0/en/union.html
MSSQL - http://msdn.microsoft.com/en-us/library/ms180026.aspx
ORACLE - https://docs.oracle.com/cd/B28359_01/server.111/b28286/queries004.htm
PostgreSQL - http://www.postgresql.org/docs/8.3/interactive/queries-union.html
etc.

Related questions

Using union and order by clause in mysql

I want to use order by with union in mysql query. I am fetching different types of record based on different criteria from a table based on distance for a search on my site. The first select query returns data …

Read More
Group by with union mysql select query

(SELECT COUNT(motorbike.`owner_id`) as count,owner.`name`,transport.`type` FROM transport,owner,motorbike WHERE transport.type='motobike' AND owner.`owner_id`=motorbike.`owner_id` AND transport.`type_id`=motorbike.`motorbike_id` GROUP BY motorbike.owner_id) UNION ALL (…

Read More
a simple way to sum a result from UNION in MySql

I have a union of three tables (t1,t2,t3). Each rerun exactly the same number of records, first column is id, second amount: 1 10 2 20 3 20 1 30 2 30 3 10 1 20 2 40 3 50 Is there a simple in sql way to sum it up to only get: 1 60 2 80 3 80

Read More