UNION's strange behavior

[Rydel]

When I run the following query:

SELECT username FROM minsk1
UNION
SELECT username FROM belarus1

Although it should not return repeated values according to the documentation (only DISTINCT ones), strangely enough, it returns dublicates, I get a query result that looks, for example, like this:

ann
john
mike
winston
zebra
ann
lucy
tom

where, for example, "ann" is repeated (from minsk1 and belarus1 tables). What's wrong with my query or with my MS Access?


Big thanks in advance!



regards,
rydel n23

http://pravapis.org/

http://rydel.net/

 

[PHV]

Have you tried this ?
SELECT DISTINCT U.username
FROM (SELECT username FROM minsk1
UNION SELECT username FROM belarus1) AS U;

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rac2]

Is it possible that you have trailing blanks? Try

SELECT RTrim(username) FROM minsk1
UNION
SELECT RTrim(username) FROM belarus1

 

[Rydel]

Neither of the two worked. :/



regards,
rydel n23

http://pravapis.org/

http://rydel.net/

 

[PHV]

I guess the two ann are different in some way.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Rydel]

But even if I right click the column head of the resulting query window and choose "sort ascending" (or descending), it still keeps the two chunks separate like this:

ann
b...
c...
zorro
ann
b..
c..
x..
y..
zorro



regards,
rydel n23

http://pravapis.org/

http://rydel.net/

 

[PHV]

Ah, very interesting.
Seems you have spurious control char at the front of some usernames.
Can you save the union query, say myQuery and try this in an brand new query sql pane:
SELECT Asc(Left(username,1)) AS test,username FROM myQuery;

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Rydel]

PHV: Wow, thank you so much! Jee, it was so stupid of me! I tried RTrim, but I never noticed that I have a space in front of each entry in minsk1 table, so I should just LTrim! I feel kind of dumb... And thank you!


regards,
rydel n23

http://pravapis.org/

http://rydel.net/