Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    Minnesota
    Posts
    7

    Exclamation Unanswered: 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:
    Code:
    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.
    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this (untested) --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Location
    Minnesota
    Posts
    7

    Thumbs up

    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.

    Code:
    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!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •