limiting LAG in SQL-CodePudding

I am using the LAG function to return a previous record. Since records are not necessarily consecutive in regards to dates I decided to go the LAG route (if there's a better way please share). The issue that I'm running into is that it gives me all records when I only want one per item. How do I go about limiting results?

Here's a data set for item a per say:

PO Number	BillingDate	DiscountRecNo	item
0646546541	12/12/2021	4654064	4656
4651132066	10/13/2021	8984653	4655
4654113866	11/13/2021	6464651	4656
9865413488	08/05/2021	5461984	4655

For this example I only want PO Number 9865413488 for item 4655 and 4654113866 for 4656.

Here's what I have

            LAG(LT.CondRecNo, 1, 0) OVER(ORDER BY LT.BillingDate DESC) AS PREVIOUSCONDRECNO

CodePudding user response：

it seems you dont need lag() just need row_number()

   with cte as
    (select *,row_number()over(partition by item order by BillingDate) rn
    ) select a.* from cte whre rn=1