In this blog you will find my t-sql notes for specific scenario's. Use them if you like!
----------------------------------------------------------------------------------------
--Get the latest date for a specific item in a table
--in this example the latest order per customer
create table #orders
(
orderid int,
customerid int,
orderdate date,
quantity int
)
INSERT INTO #orders VALUES (1,1,'2018-01-01',10)
INSERT INTO #orders VALUES (2,1,'2018-02-01',10)
INSERT INTO #orders VALUES (3,1,'2018-03-01',10)
INSERT INTO #orders VALUES (4,2,'2018-07-01',10)
INSERT INTO #orders VALUES (5,2,'2018-08-01',10)
INSERT INTO #orders VALUES (6,3,'2018-10-01',10)
;WITH CTE_LatestOrderPerCustomer AS (
SELECT
orderid,
customerid,
orderdate As LatestOrderdate
FROM (
SELECT
orderid,
customerid,
orderdate,
ROW_NUMBER() OVER (PARTITION BY customerid ORDER BY orderdate DESC) As rownumber
FROM #orders
) orders
WHERE rownumber = 1
)
SELECT * FROM CTE_LatestOrderPerCustomer
DROP TABLE #orders