Three table query to find the most recent item based on a distinct value

Question

I am attempting (without success thus far) to write a query that will join 3 tables and return the most recent record based on a distinct value.

Table A

itemnum  | itemname
-------- | --------
123      | document #1  
456      | document #2

Table B

itemnum  | keyvaluedate
-------  | ------------- 
123      | 2015-09-05 00:00:00.000  
456      | 2011-06-04 00:00:00.000  
789      | 2011-06-04 00:00:00.000  
546      | 2011-06-04 00:00:00.000  
378      | 2011-06-04 00:00:00.000

Table C

itemnum  | keyvaluebig 
-------- | -----------
123      | 123456  
456      | 123456  
545      | 987654   
387      | 987654

Table A holds all of the records. What I need to find is the most recent record (keyvaluedate from table b) for each distinct project (keyvaluebig from table c).

The itemnum data is always unique. The keyvaluedate from Table B and the keyvaluebig from Table C both non-distinct values present.

What is the best way to go about this?

score 1 · Accepted Answer · edited Apr 13 '17 at 12:43

This returns the row from table A that relates to the most recent record in table B for each keyvaluebig in table C

Setup:

DECLARE @TableA TABLE
(
    itemnum INT
    , itemname NVARCHAR(50)
);

INSERT @TableA (itemnum, itemname)
VALUES (123, 'document #1'), (456, 'document #2');

DECLARE @TableB TABLE
(
    itemnum INT
    , keyvaluedate DATETIME2
);

INSERT @TableB (itemnum, keyvaluedate)
VALUES (123, '2015-09-05 00:00:00.000'), (456, '2011-06-04 00:00:00.000')
,(789, '2011-06-04 00:00:00.000'), (546, '2011-06-04 00:00:00.000')
,(378, '2011-06-04 00:00:00.000');

DECLARE @TableC TABLE
(
    itemnum INT
    , keyvaluebig BIGINT
);

INSERT @TableC (itemnum, keyvaluebig)
VALUES (123, 123456), (456, 987654), (545, 987654), (387, 987654);

Query:

WITH cte_Numbers
AS
(
    SELECT  c.keyvaluebig
            , b.itemnum
            , ROW_NUMBER() OVER(PARTITION BY c.keyvaluebig ORDER BY b.keyvaluedate desc) AS RowNumber
    FROM    @TableB b 
    JOIN    @TableC c ON b.itemnum = c.itemnum
)

SELECT  a.*
FROM    @TableA a
JOIN    cte_Numbers n ON a.itemnum = n.itemnum
WHERE   RowNumber = 1

As mentioned by Mark Sinkinson this really is the place to go to see how to do this sort of stuff

Three table query to find the most recent item based on a distinct value

1 Answers1