From: sagar bs Date: December 4 2012 4:41am Subject: 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 List-Archive: http://lists.mysql.com/mysql/228757 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=14dae93404e16f994f04cfff7942 --14dae93404e16f994f04cfff7942 Content-Type: text/plain; charset=ISO-8859-1 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! --14dae93404e16f994f04cfff7942--