SQL query: find the row with the 2nd most recent date, 3rd most recent date, 4th most recent date and so on for each distinct entry in a table

[sagar bs]

Hi All,

I have a table in Access with data that looks like this:

ID      Value  As_of

1173    156    2009-06-01

1173    173    2008-12-01

1173    307    2008-09-01

1173    305    2008-06-01

127     209    2009-03-01

127     103    2008-11-01

127     113    2008-10-01

127     113    2008-06-01

1271    166    2009-02-01

1271    172    2008-09-01

1271    170    2008-07-01

1271    180    2008-06-01

...

What I'd like to get is the "Value" for each unique ID with the 2nd most
recent date, 3rd  most recent date, 4th  most recent date and so on "As_Of"
date

So, my result set should look like this:

ID      Value    As_of

1173    156      2008-12-01

127     209      2008-11-01

1271    166      2008-09-01

For 2nd most recent "As_Of" date.



ID      Value    As_of

1173    156      2008-09-01

127     209      2008-10-01

1271    166      2008-07-01

For 3rd most recent "As_Of" date, and so on.


Suggestions please.


Thank u!