Selecting records with most recent date for each 'order'
I have information in the format of the sample table below.  Each file can have multiple grades, I need to select the most recent grade (based on completion date) for each file.  If there is a file w/ the same completion dates, I would select the best grade (a being best and subsequent letters being a lesser grade).  This seems easy, but for some reason having a brain fart



Sample Table:


ID_PK File_No    Grade     Completion_Date

1     Smith      A         10/1/2010

2     Smith      C         9/25/2010

3     Davis      B         11/1/2010

4     Johnson    D         12/5/2010

5     Johnson    A         11/1/2010

6     Johnson    C         10/1/2010

7     Miller     X         9/1/2010

8     Miller     F         12/1/2010

9     Miller     D         10/1/2010


Ideal Results:

1     Smith      A         10/1/2010

3     Davis      B         11/1/2010

4     Johnson    D         12/5/2010

8     Miller     F         12/1/2010



Selecting records with most recent date for each 'order'
===

I have information in the format of the sample table below.  Each file can have multiple grades, I need to select the most recent grade (based on completion date) for each file.  If there is a file w/ the same completion dates, I would select the best grade (a being best and subsequent letters being a lesser grade).  This seems easy, but for some reason having a brain fart



Sample Table:


ID_PK File_No    Grade     Completion_Date

1     Smith      A         10/1/2010

2     Smith      C         9/25/2010

3     Davis      B         11/1/2010

4     Johnson    D         12/5/2010

5     Johnson    A         11/1/2010

6     Johnson    C         10/1/2010

7     Miller     X         9/1/2010

8     Miller     F         12/1/2010

9     Miller     D         10/1/2010


Ideal Results:

1     Smith      A         10/1/2010

3     Davis      B         11/1/2010

4     Johnson    D         12/5/2010

8     Miller     F         12/1/2010


