return only the newest rows for each ID

  • Thread starter Thread starter Gary B
  • Start date Start date
G

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
 
Gary B said:
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
Click to expand...

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

should do it...
 
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
 
Gary B said:
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
Click to expand...

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
 
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.
 
Gary B said:
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.
Click to expand...

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...
 
Back
Top