Most Recent Record 2

[newguy86]

Hi All,
I have a query that pulls assignment information for every PM that's attached to a project both past and present. As a result I get a table that looks like this:

Pnum assignID empID PM Role dateAdded assignStart assignEnd assignStatus
1173 00001 e000001 John Doe DM PM Primary 1/1/1900 9/26/2011 12/16/2011 Complete
1173 00002 e000002 Marty McFly DM PM Primary 1/1/1900 7/25/2011 9/23/2011 Complete
1255 00003 e000001 John Doe DM PM Primary 1/1/1900 5/3/2011 7/15/2011 Complete
1350 00004 e000003 Doctor Who DM PM Primary 1/1/1900 2/17/2011 7/31/2012 Active
1350 00005 e000004 Tony Stark DM PM Primary 1/1/1900 8/25/2011 1/19/2012 Complete
1377 00006 e000005 Mario DM PM Primary 1/1/1900 1/22/2012 4/13/2012 Active
1377 00007 e000006 Jane Doe DM PM Primary 1/1/1900 11/7/2011 2/3/2012 Complete
1377 00008 e000001 John Doe DM PM Primary 1/1/1900 2/17/2011 11/18/2011 Complete


Now what I want to do is take this information and only display the most recent assignment attached to a Pnum. For example 1173 would only show John Doe, 1350 would only show Doctor Who, and 1377 would only show Mario.

I have tried doing this using everything I can think of (First, Last, Max, DistincRow, etc.) and I cannot get anything to work.

Any suggestions?

Travis

http://www.apexsystemsinc.com

 

[PHV]

Something like this ?

SELECT A.*
FROM yourTable A INNER JOIN (
SELECT Pnum,MAX(assignEnd) AS LastDate FROM yourTable GROUP BY Pnum
) B ON A.Pnum=B.Pnum AND A.assignEnd=B.LastDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[newguy86]

I'm not sure if I'm following your SQL statement correctly but I put it in (making the appropriate assumtions for things like "yourTable", "A", etc.) and it appears to have worked.

Can you explain what the SQL is doing so that if/when I need to adjust it in the future I will know what needs to be changed?

Thanks,

Travis

http://www.apexsystemsinc.com

 

[Andrzejek]

First, run the [blue]BLUE[/blue] part of the SQL to see what is going on:

SELECT A.*
FROM yourTable A INNER JOIN ([blue]
SELECT Pnum,MAX(assignEnd) AS LastDate FROM yourTable GROUP BY Pnum[/blue]
) B ON A.Pnum=B.Pnum AND A.assignEnd=B.LastDate

Have fun.

---- Andy

 

[newguy86]

Now the SQL makes a lot more sense.

Thanks guys!

Travis

http://www.apexsystemsinc.com

 

[Andrzejek]

What a little color in your life can do....

Have fun.

---- Andy