Union query

[TimSki]

I have this query which works perfectly

SELECT TOP 10 ID, RegTime
FROM DNAccess2owners 2.access2o_admi n.t_vendeurs
UNION
SELECT TOP 10 ID, RegTime
FROM DNAccess2owners 3.access2o_admi n.t_renters
ORDER BY regtime DESC

however if i introduce another column (doesn;t seem to matter which one) like this...
SELECT TOP 10 ID, Town, RegTime
FROM DNAccess2owners 2.access2o_admi n.t_vendeurs
UNION
SELECT TOP 10 ID, Town RegTime
FROM DNAccess2owners 3.access2o_admi n.t_renters
ORDER BY regtime DESC


i get incorrect results completely different from the results given from the first query

am i missing something re union queries ??

any help is much appreciated.

[improvcornartist]

Is it just a typo that there is a missing comma after Town in the second select statement?

[TimSki]

no sorry that was my mistake. the code includes the comma. it seems the second query is kind of ignoring the order by ???

[improvcornartist]

What if you just separate the two queries by parentheses?

(SELECT TOP 10 ID, Town, RegTime 
FROM DNAccess2owners2.access2o_admin.t_vendeurs
ORDER BY regtime DESC)
UNION 
(SELECT TOP 10 ID, Town, RegTime 
FROM DNAccess2owners3.access2o_admin.t_renters
ORDER BY regtime DESC)

[TimSki]

nice idea but that throws a 'Incorrect syntax near the keyword 'ORDER'.'

my overall syntax is i believe correct. it's just the introduction of an additional col which screws things up...

[improvcornartist]

Sorry about that. I don't have SQL Server here to check syntax, but could you use something like this?

SELECT ID, Town, RegTime
FROM (
    SELECT TOP 10 ID, Town, RegTime
    FROM DNAccess2owners2.access2o_admin.t_vendeurs
    ORDER BY regtime DESC ) as t
UNION ALL
SELECT ID, Town, RegTime
FROM (
    SELECT TOP 10 ID, Town, RegTime
    FROM DNAccess2owners3.access2o_admin.t_renters
    ORDER BY regtime DESC ) as t

[ck9663]

Check the content of your table. UNION removes duplicate records on the unionized result set. If you want to return all result set, you should use UNION ALL. Also, the ORDER BY is executed on the result set. Doing a TOP clause does not insure ORDER of the result set without an ORDER BY.

All those could affect your returned result set.

Good Luck!!!

~~ CK

[TimSki]

improvcornartis t that's it ! many thanks.