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]

MYSQL query:Find the date difference  between the two dates and get the the
output in the new column.

Hi All,

I have a table in mysql  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.

or/and i need the no of days between the dates of the same ID in different
columns but the i need the distinct ID. I got the days between the 1st and
the last date, but couldnt find the date difference between the 2nd date
and the 3rd date and also 2nd date and 4th date and so on in mysql.

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.




 ID        datediff(2nd,3rd)days   datediff(2nd,4th)days

1173            91                   183

127             31                   153

1271            62                   92

I need the output like this for distinct id in different columns.

In my data i gave there are seven dates for each id so i need to get the
date difference for different combinations.


Suggestions please.


Thank u!