Selecting rows from a atabel based on a a maximum value of a DATE column
Posted on 2009-07-14
I am having a problem doing something which in mind should really be simple but I can't get my head around it.
I have an unormalised table with 10 columns, 3 of which will comprise the composite primary key. Now for any two records where this primary key is identical, the other columns may vary, inlcuding a DATE.
What I would like as a result is a table of unique values as far as the primary key goes, and the other columns with values corresponding to the rows for that primary key where the DATE value is MAX.
I've tried coming up with a query which seemed right but I ended up getting a bloody cartesian product :(
This is my table:
Where ID, REGION and YEAR will comprise the primary key and ACTION_DATE is the DATE for which I want the maximum value of, along with the its respective values for the other columns.
Any help would be greatly appreciated!