CODEWITHSUNDEEP
mysqlsqluniondistinctunion-all
cdub
cdub

Reputation: 25711

Adding DISTINCT to a UNION query

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.

Upvotes: 7

Views: 33613

Answers (3)

Alin P.
Alin P.

Reputation: 44346

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.

Upvotes: 18

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

You could drop the ALL as others have suggested.

If the two queries use same tables and are different only in the WHERE clause or only in the HAVING clause, you can also use this:

SELECT Title.id, Title.title FROM titles as Title
WHERE (1st query conditions)
   OR (2nd query conditions)

or

SELECT Title.id, Title.title FROM titles as Title
HAVING (1st query conditions)
    OR (2nd query conditions)

Upvotes: 1

Bohemian
Bohemian

Reputation: 425033

Isn't the simple way just get rid of the union and the second part altogether:

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

since HAVING points > 0 includes anything with HAVING points > 1?

Upvotes: 1

Related Questions