Thursday, March 22, 2012

Combine two tables into one RS

Here is my dilemma, i'm trying to combine the results of two different tables. Both tables are very similar but the data returned must be sorted before they are combined because I'm only returning the top xx records based on a hits column.

Here is a sample of the two databases:

Table 1
ID - SONG - HITS
1 - tb1SONG 1 - 356
2 - tb1SONG 2 - 1459
3 - tb1SONG 3 - 278
4 - tb1SONG 4 - 965
5 - tb1SONG 5 - 124

Table 2
ID - tb2SONG - HITS
1 - tb2SONG 1 - 412
2 - tb2SONG 2 - 85
3 - tb2SONG 3 - 2035
4 - tb2SONG 4 - 693
5 - tb2SONG 5 - 745

I have tried the following union query which combines the two RS's then sorts the data:
SELECT Top 2 ID, Song, Hits FROM Table1
UNION SELECT Top 2 ID, Song, Hits from Table2
Which would return the first two records from each then sort them like this:
2 - tb1SONG 2 - 1459
1 - tb2SONG 1 - 412
1 - tb1SONG 1 - 356
2 - tb2SONG 2 - 85

I would like to sort based on the hits column then combine the RS producing this:
3 - tb2SONG 3 - 2035
2 - tb1SONG 2 - 1459
4 - tb1SONG 4 - 965
5 - tb2SONG 5 - 745

Any ideas or solutions will be greatly appreciated.
Thankstry this (untested) --select *
from (
select top 2
ID
, Song
, Hits
from Table1
order by Hits
) as t1
union all
select *
from (
select top 2
ID
, Song
, Hits
from Table2
order by Hits
) as t2|||r937,

:)THANK YOU!!!:)
That worked great. I threw a Order By at the very end of your code and it sorted both together exactly as I wanted.

select *
from (
select top 2
ID
, Song
, Hits
from Table1
order by Hits
) as t1
union all
select *
from (
select top 2
ID
, Song
, Hits
from Table2
order by Hits
) as t2 ORDER BY Hits

Thanks Again!!!

No comments:

Post a Comment