B Adams B Adams - 6 months ago 9
SQL Question

TSQL query to return most recent record based on another columns value

I have a table that contains a list of expiration dates for various companies. The table looks like the following:

ID CompanyID Expiration
--- ---------- ----------
1 1 2016-01-01
2 1 2015-01-01
3 2 2016-04-02
4 2 2015-04-02
5 3 2014-01-03
6 4 2015-04-09
7 5 2015-07-20
8 5 2016-05-01


I am trying to build a TSQL query that will return just the most recent record for every company (i.e. CompanyID). Such as:

ID CompanyID Expiration
--- ---------- ----------
1 1 2016-01-01
3 2 2016-04-02
5 3 2014-01-03
6 4 2015-04-09
8 5 2016-05-01

Answer

It looks like there is a exact correlation between ID and Expiration. If that is true, ie the later the Expiration the higher the ID, then you could simply pull Max(ID) and Max(Expiration) which are 1:1 and group by CompanyID:

Select max(ID), CompanyID, max(Expiration) from Table group by Company ID
Comments