CodingForums.com - View Single Post - Issue with DISTINCT and ORDER BY with UNION

Old Pedant · 10-04-2012, 12:17 AM

Can you show the result of doing *JUST* the UNION part of that?

And you only show 3 records in the "returns like this". I assume it is actually returning many more?

And you apparently misunderstand how SELECT DISTINCT works.

It applies to *ALL* the fields in your SELECT. If *ANY* fields in the two records are different, then the records are different and the DISTINCT will show them.

Putting parentheses around the field name, as you did with

Code:

SELECT DISTINCT(p.postId), p.roomId, ...

is meaningless. It changes nothing. Remove them and you will get identical results.

10-04-2012, 12:17 AM	PM User \| #2
Old Pedant Supreme Master coder! Join Date: Feb 2009 Posts: 23,187 Thanks: 59 Thanked 3,995 Times in 3,964 Posts	Can you show the result of doing JUST the UNION part of that? And you only show 3 records in the "returns like this". I assume it is actually returning many more? And you apparently misunderstand how SELECT DISTINCT works. It applies to ALL the fields in your SELECT. If ANY fields in the two records are different, then the records are different and the DISTINCT will show them. Putting parentheses around the field name, as you did with Code: SELECT DISTINCT(p.postId), p.roomId, ... is meaningless. It changes nothing. Remove them and you will get identical results. __________________ An optimist sees the glass as half full. A pessimist sees the glass as half empty. A realist drinks it no matter how much there is.