CODEWITHSUNDEEP
sqlsql-servert-sql
SQLJax
SQLJax

Reputation: 612

Displaying a sorted dataset from two tables

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

Upvotes: 0

Views: 82

Answers (4)

T I
T I

Reputation: 9943

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

Upvotes: 0

Declan_K
Declan_K

Reputation: 6826

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)

Upvotes: 2

roman
roman

Reputation: 117485

;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

Upvotes: 0

Michael Goldshteyn
Michael Goldshteyn

Reputation: 74410

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

Upvotes: 3

Related Questions