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:
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
So how does this work? According to the Microsoft Docs:
You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.
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.