question

emil avatar image
emil asked

extract the most recent dated records

I have a table that contains 3 column: Item code, invoice date and invoice number. I want to extract the most recent inv.dated records for each item. ITEM- INVDATE- INVNUM- 221005329- 2013.01.10- 501409- 221005329- 2012.11.29- 1203819- 260058142- 2013.03.13- 501918- 260058142- 2012.10.31- 1203664-
sql querydate
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Bhupendra99 avatar image
Bhupendra99 answered
Hi To give you exact idea I had created a table and inserted all the sample data that u had mention in it Declare @Invoice Table ( Item INT, InvDate DateTime, InvNum INT ) Insert Into @Invoice Select 221005329,'2013-01-10',501409 UNION ALL Select 221005329,'2013-11-29',1203819 UNION ALL Select 260058142,'2013-03-13',501918 UNION ALL Select 260058142,'2012-10-31',1203664 ----Use this query replace @Invoice with your actual table Select * from ( Select ROW_NUMBER() OVER (Partition By Item Order by InvDate DEsc) RNO,* from @Invoice )A WHERE RNO=1
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

emil avatar image
emil answered
It works, thank you for your quick and clear answer.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.