Most recent date row with two unique IDS

Question

I am trying to create a query that will pull the most recent row for a state and tier3ID combination, while also showing the other three columns.

EFFECTIVE_DATE|STATE|ELIGIBLE|INTONLY|AOPELIGIBLE|TIER3ID
01-JAN-05     | AK  | Yes    | No    | No        |1101
15-NOV-12     | IN  | Yes    | No    | No        |1101
01-JAN-05     | AZ  | Yes    | No    | No        |1101

Everything I have tries gets me the data combinations, but I cant display the other three rows unless I group by them as well.

I basically want:

select state, tier3id, eligible,
max(effective_date)
from thirdtiereligibilityeq
group by state, tier3id, eligible

While having eligible, into only, and AOPeligible displaying for those rows as well.

possible duplicate of [Fetch the row which has the Max value for a column](http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) — Bill Karwin, Nov 20 '12 at 00:21
I added the `greatest-n-per-group` tag. This question has been answered 500 times on Stack Overflow. — Bill Karwin, Nov 20 '12 at 00:22
The solution depends on which RDBMS you use. You neglected to mention that crucial detail. For PostgreSQL look at [SQL: Select first row in each GROUP BY group?](http://stackoverflow.com/q/3800551/939860) — Erwin Brandstetter, Nov 20 '12 at 00:28

score 0 · Answer 1 · answered Sep 05 '14 at 09:32

Here is the query you're looking for:

SELECT T.*
FROM thirdtiereligibilityeq T
INNER JOIN (SELECT T2.state
                  ,T2.tier3id
                  ,MAX(T2.effective_date) AS [last_effective_date]
            FROM thirdtiereligibilityeq T2
            GROUP BY T2.state, T2.tier3id) L ON L.state = T.state
                                               AND L.tier3id = T.tier3id
                                               AND L.last_effective_date = T.effective_date

Hope this will help you.

Most recent date row with two unique IDS

1 Answers1