Return most recent rows in each group

[dpdougla]

I have a data set similar to this:

id assign_no last_attempt DPD Status
--------------------------------------------------
1 123456 12/31/2010 100 On Hold
2 123456 12/10/2010 50 On Hold
3 123456 12/5/2010 25 On Hold
4 123457 12/28/2010 90 Closed
5 123457 12/10/2010 80 Closed
6 123457 12/5/2010 60 Closed

Note there are two basic sets of records here: 123456 and 123457. What I want is to get the most recent attempts in these two groups. The desired set returned would be this:

id assign_no last_attempt DPD Status
---------------------------------------------------
1 123456 12/31/2010 100 On Hold
4 123457 12/28/2010 90 Closed

I've tried all kinds of aggregate queries and it always returns the same 6 rows instead of the 2 that I want.

SELECT assign_no, MAX(last_attempt) AS last_attempt,
DPD, Status
FROM dbo.Test
GROUP BY assign_no, DPD, Status

(This doesn't work)

Thanks for all your help!

 

[dpdougla]

I get the results I want in MS Access using the first aggregate function, but this won't work in SQL Server (which is the back-end database I'm working with)

MS Access query:

SELECT First(Test.id) AS FirstOfid, Test.assign_no, First(Test.last_attempt) AS FirstOflast_attempt, First(Test.DPD) AS FirstOfDPD, Test.Status
FROM Test
GROUP BY Test.assign_no, Test.Status;

Results:

FirstOfid assign_no FirstOflast_attempt FirstOfDPD Status
---------------------------------------------------------------------------------
1 123456 12/31/2010 100 On Hold
4 123457 12/28/2010 90 Closed

 

[markros]

Take a look at the following blogs:

Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2

They explain various SQL Server solutions for the problem above.

PluralSight Learning Library

 

[dpdougla]

I experimented with a lot of different queries including some of the ones suggested above.

This one using MAX DISTINCT worked perfect:

Select assign_no,
MAX(DISTINCT CONVERT(varchar, last_attempt, 101)) AS last_attempt,
Status,
MAX(DISTINCT DPD) AS DPD
FROM Test
GROUP BY assign_no, Status
ORDER BY assign_no DESC

On quite a few tests that I ran I got exactly the results that I had expected.

However, I fear that this may only work on SQL Server 2005. Our production server uses 2000. If this is the case I may end up going back to the drawing board.

Do you know if MAX DISTINCT is supported in 2005?

 

[markros]

I don't know where did you get MAX(Distinct from. I never knew you can do MAX(Distinct and I don't think it's the query you need to use for your problem. Did you try checking the links I gave you?

PluralSight Learning Library