CODEWITHSUNDEEP
sqlsql-server-2008
inutan
inutan

Reputation: 10888

Is there a better way - Union All/Order By

I have two tables with data as -

TestTable1                          TestTable2  
----------                          ----------  
Id  Name        DealDate            Id  Name        DealDate  
1   aaTable     2010-09-22          1   aaTable2    2010-09-23  
2   bbTable     2010-09-23          2   bbTable2    2010-09-24  
3   ccTable     2010-09-28          3   ccTable2    2010-09-26  
4   ddTable     2010-09-25          4   ddTable2    2010-09-27

I want to have only two latest records from resultset of TestTable1 + TestTable2 to return this - 

Id  Name        DealDate
4   ccTable     2010-09-28 
3   ddTable2    2010-09-27

This is my current query 

SELECT TOP 2 * FROM 
(
    SELECT * FROM
    (SELECT TOP 2 Id, Name, DealDate FROM TestTable1 ORDER BY DealDate DESC) T1
    UNION ALL 
    SELECT * FROM
    (SELECT TOP 2 Id, Name, DealDate FROM TestTable2 ORDER BY DealDate DESC) T2
) T1T2
ORDER BY DealDate DESC

Can anyone please guide me for a better way (performance-wise/readability wise) to achieve this?

P.S. Above tables are just to present the use-case, my actual tables have thousands of records.

Thank you!

Upvotes: 1

Views: 128

Answers (2)

Arj
Arj

Reputation: 2046

If you always wanted to have 1 record from TestTable1 and 1 record from TestTable2, then you could try:

SELECT TOP 1 * FROM TestTable1 ORDER BY DealDate DESC
UNION ALL
SELECT TOP 1 * FROM TestTable2 ORDER BY DealDate DESC

Otherwise the other answers should be what you're looking for.

Upvotes: 0

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174329

The only thing I would change performance wise is to remove the ORDER BY and the TOP 2 in the inner selects - they are pointless.
Other than that, your solution is the way to go. There shouldn't be any performance problems as you are using UNION ALL and not UNION...

SELECT TOP 2 * FROM 
(
    SELECT Id, Name, DealDate FROM TestTable1
    UNION ALL 
    SELECT Id, Name, DealDate FROM TestTable2
) T1T2
ORDER BY DealDate DESC

Upvotes: 4

Related Questions