Finding the last date a date stamped item shows up in a table (SQL)

So, I was writing a query to retrieve data from a Dynamics GP table. There was a question about when certain codes were last used. So, my thought process took me down the path of saying I could write a query that pulls all of the distinct codes from the table like so:

https://gist.github.com/jshinevar/971c0fe2dc137a11859a0b1c86a1f5f2

However, there is also a date column for when the code is used. This is because it is a transaction table. So, if I add in the date stamp, I will get tons of rows returned as a result of the query. I needed some way to only get the last date stamp that each code was used. For that I used the following code:

SELECT DISTINCT Code
, MAX(Date) OVER (PARTITION BY Code) Date
FROM QueryTable

So how does this work? According to the Microsoft Docs:

I guess this is a good time as ever to say that I am using SQL Server, and this applied to SQL Server 2008 and beyond. So, this essentially makes a group of each code, and then uses the aggregate function MAX to find the maximum date used per group of code. Now I can say when the last time a specific code was used.

Originally published at http://jamesshinevar.com on October 11, 2017.

Geek Dad, Software Engineer, Backyard Grill Master, Best Husband I can be.