Problem with top when using union

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with top when using union

Author

Topic

raghav_sai2002
Starting Member

18 Posts

Posted - 2009-08-19 : 10:01:09

hi,
i am stuck with a problem. i have tried for a solution and i ran out of ideas.please help me out with this one.

i have 2 table which are similiar in columns ( lets say tabe2 copy of table1).
now when i do a straight query on the table2.. i am getting expected results.

i need to do this for an update value depending upon the selected column value

select top 10 * from tb1dcc074b3811b4a3c89e11a15a1c5a472 a
RIGHT JOIN tb2dcc074b3811b4a3c89e11a15a1c5a472 b
ON a.col1 = b.col1
AND a.col2 = b.col2
AND a.col3 = b.col3
order by column4 desc

i need to get the union of both the table data and show that as a report

select * from
(
select col1,col2, col3, column4 , col5
from tb1dcc074b3811b4a3c89e11a15a1c5a472 a
LEFT JOIN tb2dcc074b3811b4a3c89e11a15a1c5a472 b
ON a.col1 = b.col1
AND a.col2 = b.col2
AND a.col3 = b.col3
UNION

select col1,col2, col3, column4 , col5
from tb1dcc074b3811b4a3c89e11a15a1c5a472 a
RIGHT JOIN tb2dcc074b3811b4a3c89e11a15a1c5a472 b
ON a.col1 = b.col1
AND a.col2 = b.col2
AND a.col3 = b.col3

)a
order by column4 desc

this doesn't produce the same results as the above , even though the first query in the union doesnt return any rows ( i.e select col1,col2, col3, column4 , col5
from tb1dcc074b3811b4a3c89e11a15a1c5a472 a
LEFT JOIN tb2dcc074b3811b4a3c89e11a15a1c5a472 b
ON a.col1 = b.col1
AND a.col2 = b.col2
AND a.col3 = b.col3 )

there is no problem if i dont use the top in the select statement, but if i use the top then i get some other result than the one i expect.

so please do help me out, and let me know if i need to do anything.
thanks for your help in advance

...sai.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-19 : 14:04:48

What data do you actually want returned?

You shown us what you have tried but not what you want.

[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]

do you want this?

select * from 
(
select TOP 10 col1,col2, col3, column4 , col5
from tb1dcc074b3811b4a3c89e11a15a1c5a472 a
LEFT JOIN tb2dcc074b3811b4a3c89e11a15a1c5a472 b
ON a.col1 = b.col1
AND a.col2 = b.col2
AND a.col3 = b.col3 
order by column4 desc

UNION ALL

select TOP 10 col1,col2, col3, column4 , col5
from tb1dcc074b3811b4a3c89e11a15a1c5a472 a
RIGHT JOIN tb2dcc074b3811b4a3c89e11a15a1c5a472 b
ON a.col1 = b.col1
AND a.col2 = b.col2
AND a.col3 = b.col3 
order by column4 desc

)a
order by column4 desc

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-19 : 14:16:08

Is this what you want?



select top 10 coalesce(a.col1,b.Col1) as Col1,coalesce(col2,b.Col2) as Col2, coalesce(a.col3,b.Col3) as Col3, coalesce(a.column4,b.Column4) as Column4 , coalesce(a.col5,b.Col5) as Col5
from tb1dcc074b3811b4a3c89e11a15a1c5a472 a
Full outer JOIN 
	tb2dcc074b3811b4a3c89e11a15a1c5a472 b
ON a.col1 = b.col1
AND a.col2 = b.col2
AND a.col3 = b.col3 
order by column4

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

raghav_sai2002
Starting Member

18 Posts

Posted - 2009-08-21 : 00:58:00

hi,
thank you for the reply, and also sorry for my incomplete question.
basically i was asking for clarification in the sense that
why does the order of retrieval of records for top and order by is not the same before union and after the union even though there are records in only one result set.

from next time on wards i will try to be more clear and specific. and i will definitely go through the link which you gave me for posting questions.
thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources