Any suggestions on how to revise code to accomodate order by issue

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 DESChave you tried this: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 ...|||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!!!