How to select rows based on two columns creating an identifier and the max date

Question

I have six columns. One of the six columns I created myself. It is two of the columns put together to create an identifier column. I want to select only the max date row for each distinct combination of the identifier column. When I omit the quantity column, I get the expected number of rows. However, once I add in quantity it gives me rows I don't expect. How do I select only the max date rows for each distinct occurrence of my Identifier column?

For example, when I run this query...

Select 
 Distinct(L.ItemNo+' 'L.Lot) as Identifier
 ,Max(L.PostingDate)
 ,L.ItemNo
 ,L.Description
 ,L.Quantity
 ,L.Lot
From dbo.JournalLine L
Groupy by
 L.ItemNo
 ,L.Lot
 ,L.Description
 ,L.Quantity

I get the below results. The row here that I am not expecting is the row with 45 Apples.

Identifier	PostingDate	ItemNo	Description	Quantity	Lot
I123 LOT123	2021-06-01	I123	Celery	79	L123
I456 LOT456	2021-06-01	I456	Carrot	25	L456
I456 LOT654	2021-06-01	I654	Carrot	21	L654
I789 LOT789	2021-05-28	I789	Apple	45	L789
I789 LOT789	2021-06-01	I789	Apple	38	L789
I789 LOT555	2021-06-01	I789	Apple	11	L555

Answer 1

Use window functions MAX() and FIRST_VALUE() to get the values of PostingDate and Quantity respectively of the row with the latest PostingDate:

SELECT DISTINCT
       ItemNo + ' ' + Lot AS Identifier,
       MAX(PostingDate) OVER (PARTITION BY ItemNo, Lot, Description) AS PostingDate,
       ItemNo,
       Description,
       FIRST_VALUE(Quantity) OVER (PARTITION BY ItemNo, Lot, Description ORDER BY PostingDate DESC) AS Quantity,
       Lot
FROM dbo.JournalLine