Suppose I have the following table named casehistory:
| caseid | statusid | value | latestupdate |
|---|---|---|---|
| id1 | 1234 | rec | 2015-10-06 |
| id1 | 1998 | in | 2014-10-10 |
| id1 | 1456 | cls | 2016-10-21 |
| id2 | 1234 | rec | 2019-01-04 |
| id2 | 1456 | cls | 2019-07-16 |
| id3 | 1234 | rec | 2012-12-03 |
| id3 | 1998 | rec | 2012-12-10 |
| id3 | 1456 | rec | 2012-12-15 |
| id3 | 1782 | dec | 2012-12-25 |
I would like to get to this table:
| caseid | statusid | value | latestupdate |
|---|---|---|---|
| id1 | 1456 | cls | 2016-10-21 |
| id2 | 1456 | cls | 2019-07-16 |
| id3 | 1782 | dec | 2012-12-25 |
I cannot seem to get the SQL correct for achieving this. I am able to use MAX() and GROUP BY to achieve the table below, but I don't know how to get the desired result above. The issue is when I include the value column in the query and the GROUP By clause, the caseid's get duplicated and returns the first table above
Please help. The solution seems obvious but I can't seem to get there. Including the value column and having only the latest records show is essential.
| caseid | latestupdate |
|---|---|
| id1 | 2016-10-21 |
| id2 | 2019-07-16 |
| id3 | 2012-12-25 |
