return only the newest rows for each ID

[Gary B]

Using the following table data:

TableName = ClaimsHistory

PrimaryKey ClaimID
1 1
2 1
3 2
4 2
5 2
6 3


How do I return just the "newest" row for each unique ClaimID?

If properly done, I would return:

PrimaryKey ClaimID
2 1
5 6
6 3

 

[Amy Blankenship]

Using the following table data:

TableName = ClaimsHistory

PrimaryKey ClaimID
1 1
2 1
3 2
4 2
5 2
6 3


How do I return just the "newest" row for each unique ClaimID?

If properly done, I would return:

PrimaryKey ClaimID
2 1
5 6
6 3

SELECT Max(PrimaryKey) as RecentKey FROM ClaimsHistory Group By ClaimID

should do it...

 

[Gary B]

What if I complicate things further by the following:

Using the following table data:

TableName = ClaimsHistory

PrimaryKey ClaimDateTime ClaimID
1 04/01/2008 10:10:01 1
2 04/01/2008 10:10:02 1
3 04/01/2008 10:10:04 2
4 04/01/2008 10:10:05 2
5 04/01/2008 10:10:03 2
6 04/01/2008 10:10:03 2
7 04/01/2008 10:10:03 2
8 04/01/2008 10:10:07 3
9 04/01/2008 10:10:06 3

How do I return just the "newest" row for each unique ClaimID?
BUT, I need to first consider the DateTime, then the PrimaryKey

If properly done, I would return:

PrimaryKey ClaimID
2 1
7 2
8 3

 

[Amy Blankenship]

What if I complicate things further by the following:

Using the following table data:

TableName = ClaimsHistory

PrimaryKey ClaimDateTime ClaimID
1 04/01/2008 10:10:01 1
2 04/01/2008 10:10:02 1
3 04/01/2008 10:10:04 2
4 04/01/2008 10:10:05 2
5 04/01/2008 10:10:03 2
6 04/01/2008 10:10:03 2
7 04/01/2008 10:10:03 2
8 04/01/2008 10:10:07 3
9 04/01/2008 10:10:06 3

How do I return just the "newest" row for each unique ClaimID?
BUT, I need to first consider the DateTime, then the PrimaryKey

If properly done, I would return:

PrimaryKey ClaimID
2 1
7 2
8 3

So, what you're saying is that you want to return the Primary key that
corresponds to the most recent time, not the highest Primary key?

SELECT PrimaryKey from ClaimsHistory INNER JOIN
(SELECT (Max(ClaimDateTime) AS CDT, ClaimID AS CID FROM ClaimsHistory AS CH
GROUP BY ClaimID)
ON ClaimsHistory.ClaimDateTime=CH.CDT;

HTH;

Amy

 

[Gary B]

Amy,

Thanks so much for your replies!

I just need to say that ClaimDateTime will in some instances be the exact
same value, so if that were the case, I needed the solution to then rely on
PrimaryKey to supply the order.

So if the dates are ever the same, then I want the highest PrimaryKey to
prevail.

Thanks again for any additional comments.

 

[Amy Blankenship]

Amy,

Thanks so much for your replies!

I just need to say that ClaimDateTime will in some instances be the exact
same value, so if that were the case, I needed the solution to then rely
on PrimaryKey to supply the order.

So if the dates are ever the same, then I want the highest PrimaryKey to
prevail.

Thanks again for any additional comments.

Take the query I gave you last time and then build another on top of it
joining back to the table and select the Max PrimaryKey Grouping on
ClaimID...