Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    3

    Unanswered: Help! Union Problem

    The code below needs to do the following: SELECT the TOP 5 most recent entries from the table and UNION that data with the next table which performs the same SELECT statement.

    I am not getting the correct output due to: SQL does not allow ORDER BY between a UNION so I am not pulling the most recent entries.

    Any suggestion on how to solve this problem?

    Thanks!

    SELECT TOP 5 GlobalBanking.dbo.GB_LTFLead.BankCode AS BANKCODE, GlobalBanking.dbo.GB_LTFLead.Date AS DATE,
    GlobalBanking.dbo.GB_LTFLead.code AS CODE, GlobalBanking.dbo.GB_LTFLead.Deal AS DEAL,
    ISNULL('Fee: $'+GlobalBanking.dbo.GB_LTFLead.Fee, '') AS FEE,
    ISNULL(NULL, RIGHT(GlobalBanking.dbo.GB_LTFLead.date,4)) AS YEAR
    FROM GlobalBanking.dbo.GB_LTFLead WHERE GlobalBanking.dbo.GB_LTFLead.BankCode = 1

    UNION ALL

    SELECT TOP 5 GlobalBanking.dbo.GB_LTFSyndicate.BankCode AS BANKCODE, GlobalBanking.dbo.GB_LTFSyndicate.Date AS DATE,
    'Syndicate' AS CODE, GlobalBanking.dbo.GB_LTFSyndicate.Deal AS DEAL,
    ISNULL('Fee: $' + GlobalBanking.dbo.GB_LTFSyndicate.Fee, '') AS FEE,
    ISNULL(NULL, RIGHT(GlobalBanking.dbo.GB_LTFSyndicate.Date,4)) AS 'YEAR'
    FROM GlobalBanking.dbo.GB_LTFSyndicate WHERE GlobalBanking.dbo.GB_LTFSyndicate.BankCode = 1

    UNION ALL

    SELECT TOP 5 GlobalBanking.dbo.GB_SecLead.BankCode AS BANKCODE, GlobalBanking.dbo.GB_SecLead.Date AS DATE,
    GlobalBanking.dbo.GB_SecLead.code AS CODE, GlobalBanking.dbo.GB_SecLead.Deal AS DEAL,
    ISNULL('Fee: $'+GlobalBanking.dbo.GB_SecLead.Fee, '') AS FEE,
    ISNULL(NULL, RIGHT(GlobalBanking.dbo.GB_SecLead.date,4)) AS YEAR
    FROM GlobalBanking.dbo.GB_SecLead WHERE GlobalBanking.dbo.GB_SecLead.BankCode = 1

    UNION ALL

    SELECT TOP 5 GlobalBanking.dbo.GB_SecSyndicate.BankCode AS BANKCODE, GlobalBanking.dbo.GB_SecSyndicate.Date AS DATE,
    'Syndicate' AS CODE, GlobalBanking.dbo.GB_SecSyndicate.Deal AS DEAL,
    ISNULL('Fee: $' + GlobalBanking.dbo.GB_SecSyndicate.Fee, '') AS FEE,
    ISNULL(NULL, RIGHT(GlobalBanking.dbo.GB_SecSyndicate.Date,4)) AS 'YEAR'
    FROM GlobalBanking.dbo.GB_SecSyndicate WHERE GlobalBanking.dbo.GB_SecSyndicate.BankCode = 1

    ORDER BY YEAR DESC
    Last edited by skyysthelimit; 12-02-04 at 17:40. Reason: better title

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    have you tried this:
    Code:
    select * from 
    ( SELECT TOP 5 GlobalBanking.dbo.GB_LTFLead.BankCode AS BANKCODE
           , GlobalBanking.dbo.GB_LTFLead.Date AS DATE
           , GlobalBanking.dbo.GB_LTFLead.code AS CODE
           , GlobalBanking.dbo.GB_LTFLead.Deal AS DEAL
           , ISNULL('Fee: $'+GlobalBanking.dbo.GB_LTFLead.Fee, '') AS FEE
           , ISNULL(NULL, RIGHT(GlobalBanking.dbo.GB_LTFLead.date,4)) AS YEAR 
        FROM GlobalBanking.dbo.GB_LTFLead 
       WHERE GlobalBanking.dbo.GB_LTFLead.BankCode = 1 
      order by GlobalBanking.dbo.GB_LTFLead.date desc
    ) as derivedtable1   
    
    UNION ALL 
    
    select * from 
    ( SELECT ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2004
    Posts
    3

    Thanks r937!!

    Code works great! I only had to add an additional Select statement at the beginning in order to combine everything I.E.:

    SELECT * FROM
    (select * from
    ( SELECT TOP 5 GlobalBanking.dbo.GB_LTFLead.BankCode AS BANKCODE
    , GlobalBanking.dbo.GB_LTFLead.Date AS DATE
    , GlobalBanking.dbo.GB_LTFLead.code AS CODE
    , GlobalBanking.dbo.GB_LTFLead.Deal AS DEAL
    , ISNULL('Fee: $'+GlobalBanking.dbo.GB_LTFLead.Fee, '') AS FEE
    , ISNULL(NULL, RIGHT(GlobalBanking.dbo.GB_LTFLead.date,4)) AS YEAR
    FROM GlobalBanking.dbo.GB_LTFLead
    WHERE GlobalBanking.dbo.GB_LTFLead.BankCode = 1
    order by GlobalBanking.dbo.GB_LTFLead.date desc
    ) as derivedtable1

    UNION ALL

    select * from
    ( SELECT ...)) AS DERIVETABLE2
    ORDER BY DATE DESC


    Other than that it runs well. Your great!!!

Posting Permissions

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