Displaying a sorted dataset from two tables

Question

I am attempting to build a view which selects from an active and archive table, but only returns a record from the archive folder when the record does not exist in the active folder. It must also only return the archive record with the latest timestamp.

Thank you

Example:

Active:

*ID  ItemID   Price*
1    001     1.00

2    002     4.99

3    004     2.00

Archive

*ID ItemID   Price   ArchiveDate*

1    001    0.99    1/1/2013

2    002    3.78    1/1/2013

3    003    5.00    1/5/2013

4    005    3.49    1/5/2013

5    003    6.99    1/10/2013

Should return the following dataset

*ItemID Price*

001     1.00

002     4.99

003     6.99   <-- From Archive

004     2.00

005     3.49   <-- From Archive

Had to change my accepted answer as the Order By in Michael's answer was not accepted in the my view. Either way I appreciate the help. Thank you all. — SQLJax, Aug 08 '13 at 20:44

score 3 · Answer 1 · answered Aug 08 '13 at 20:09

3

You can use the following:

SELECT ItemID, Price
FROM 
(
-- All of the active records
SELECT ItemID, Price 
FROM Active 
UNION ALL    
-- combined with the latest archive records for missing actives
SELECT ItemID, Price 
FROM Archive AS A1
WHERE ArchiveDate=(SELECT MAX(ArchiveDate) 
                   FROM Archive AS A2
                   WHERE A2.ItemID=A1.ItemID)
      AND NOT EXISTS(SELECT 1 
                     FROM Active AS AC 
                     WHERE AC.ItemID=A1.ItemID)
) AS FinalResult
-- Ordered by ItemID
ORDER BY ItemID

answered Aug 08 '13 at 20:09

Michael Goldshteyn

71,784
24
131
181

1

Why the outer select? Also, please don't recommend `SELECT * ` in an EXISTS or NOT EXISTS clause - it is bad practice. – Declan_K Aug 08 '13 at 20:13
The outer select is for ordering purposes of the results of the `UNION ALL`. Changed the `EXISTS` to use `SELECT 1`, but I am not sure why it is a *bad* practice, since SQL Server doesn't care much about the column list in an `EXISTS` expression. – Michael Goldshteyn Aug 08 '13 at 20:14
2

Unless you're perpetuating the [SELECT * myth](http://stackoverflow.com/questions/6137433/where-does-the-practice-exists-select-1-from-come-from). – Michael Goldshteyn Aug 08 '13 at 20:22
Great Link! Thank you. I will never suggest it again, and now I have ammo for those who complain when I use it... ;-) +1 – Declan_K Aug 08 '13 at 20:31

score 2 · Accepted Answer · answered Aug 08 '13 at 20:11

So you need to get everthing from ACTIVE [First part of the UNION below[, then append the records from ARCHIVE [Second part of the UNION[. For the Archive records, exlcude those that dont exist in the ACTIVE table [The NOT EXISTS clause] and only get the record that has the latest date [THE MAX(ArchiveDate)] clause.

SELECT  ItemID
        ,Price
FROM    ACTIVE
UNION
SELECT  ItemID
        ,Price
FROM    ARCHIVE AA
WHERE   AA.ArchiveDate = (SELECT MAX(ArchiveDate) FROM ARCHIVE AA1 WHERE AA1.ItemID = AA.ItemID)
AND     NOT EXISTS (SELECT 1 FROM ACTIVE A WHERE A.ItemID = AA.ItemID)

score 0 · Answer 3 · answered Aug 08 '13 at 20:11

;with cte_archive as (
     select
         ItemID, Price,
         row_number() over (partition by ItemID order by ArchiveDate desc) as row_num
     from Archive 
)
select AC.ItemID, AC.Price
from Active as AC

union all

select AH.ItemID, AH.Price
from cte_archive as AH
where AH.ItemID not in (select ItemID from Active) and AH.row_num = 1

score 0 · Answer 4 · answered Aug 08 '13 at 20:30

SELECT ItemID, Price
FROM Active
UNION ALL
SELECT ItemID, Price
FROM (
    SELECT ItemID, Price
    FROM (
        SELECT ItemID, Price, 
               ROW_NUMBER() OVER(PARITION BY ItemID ORDER BY ArchiveDate DESC) rn
        FROM Archive
        WHERE NOT EXISTS(SELECT 1 FROM Active a WHERE a.ItemID = Active.ItemID)
    ) sub
    WHERE rn=1 
) Archive

Displaying a sorted dataset from two tables

4 Answers4