cliffgettings (8/29/2014)

---

Jack Corbett (8/29/2014)

---

Sounds like a good use for RowNumber() something like this (since I don't have your schema I'm just guessing at some things see the first link in my signature for some tips on posting):

WITH Cases

AS (

SELECT

*,

ROW_NUMBER() OVER (PARTITION BY CaseID ORDER BY LastUpdated DESC) AS RowNo /* Assuming there is a date that is populated when a case is changed */

FROM

dbo.CaseHistory

)

SELECT

*

FROM

Cases

WHERE

Cases.RowNo = 1

Thanks Jack, but I don't have a 'last updated' field. All I have it the order I it appears in the table, data is appended each time (I didn't design this table).. Is there a way to assign an index (not sure if that's the right term) so that if there are caseID's duplicated, it only pulls the one's furthest down the table (the latest one that's appended).

ps. When typing up the sample table for posting here, I made a mistake and all the jack smith's should have the same caseID.