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
www.apexsystemsinc.com
 

Something like this ?

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

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
www.apexsystemsinc.com
 

    
First, run the BLUE part of the SQL to see what is going on:

Have fun.

---- Andy

Now the SQL makes a lot more sense.

Thanks guys!

Travis
www.apexsystemsinc.com
 

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

Have fun.

---- Andy