CODEWITHSUNDEEP
sqlsql-server
Irwan Rahman S
Irwan Rahman S

Reputation: 3

Select which has the latest date for each item

I have 2 tables, let say tableA and tableB.

Table A

create table tableA
(ID int, DocumentDate datetime2, DocumentName varchar(3), ItemID int);

insert into tableA (ID, DocumentDate, DocumentName, ItemID)
values (1, '2019-08-01 12:00:00', 'A-1', 1),
(2, '2020-05-12 13:00:00', 'B-2', 1),
(3, '2021-07-01 14:00:00', 'C-3', 1),
(4, '2020-01-01 12:00:00', 'D-4', 2),
(5, '2021-02-01 13:00:00', 'E-5', 2),
(6, '2021-07-02 14:00:00', 'F-6', 2);

and this is table B

create table tableB
(ID int, ItemCode varchar(3));

insert into tableB (ID, ItemCode)
values (1, 'AAA'),
(2, 'BBB');

here is my SQL Server query

select 
    A.ID, 
    A.DocumentDate, 
    A.DocumentName, 
    B.ItemCode 
from tableA A
left join tableB B on B.ID = A.ItemID

and the result should be like this

I want to select the 3rd one for AAA and the 6th one for BBB, which has the latest date. Thank You.

Upvotes: 0

Views: 43

Answers (2)

Richard Deeming
Richard Deeming

Reputation: 31248

Seems simple enough:

WITH cteDocuments As
(
    SELECT
        ID,
        DocumentDate,
        DocumentName,
        ItemID,
        ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY DocumentDate DESC) As RN
    FROM
        tableA
)
SELECT
    A.ID,
    A.DocumentDate,
    A.DocumentName,
    B.ItemCode
FROM
    cteDocuments As A
    LEFT JOIN tableB As B ON B.ID = A.ItemID
WHERE
    A.RN = 1
;

ROW_NUMBER

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can use apply:

select b.*, a.*
from tableB b outer apply
     (select top (1) a.*
      from tableA a
      where a.itemId = b.Id
      order by a.documentdate desc
     ) a;

With an index on tableA(item, documentdate), this would often have very good performance

Upvotes: 1

Related Questions