Thanks Peter - you gave me some ideas... here is what I have so far (simplified for simplification..)
select t2.dt_aud_rec, t1.id_secr_rqst from isr2_aud_log t1, isr2_aud_log t2 where t1.id_secr_rqst=t2.id_secr_rqst and t1.dt_aud_rec > t2.dt_aud_rec group by t1.id_secr_rqst but the problem is that it only returns the record related to the second largest date for each id_secr_rqst. any suggestions how to get both the second and the largest date records in the same query? Thanks, Anoop On 5/23/05, Peter Normann <[EMAIL PROTECTED]> wrote: > > Anoop kumar V <mailto:[EMAIL PROTECTED]> wrote: > > > well - actually it might not be the last 2 days - i just want 2 of > > the latest records for every task regardless of what date it is in > > the table. > > Okay, now I think I understand what you need - and if I am correct, this > looks like one of the more exotic querys to me, but then again, I'm not > like > the SQL king around, but let me give it a shot: > > SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec, MAX(dt_aud_rec) > Latest, MAX(dt_aud_rec) NoSoLatest > FROM isr2_aud_log t1, isr2_aud_log t2 > WHERE t1.id_secr_rqst = t2.id_secr_rqst > AND t1.name_rec_type='Exception Resource' > AND dt_aud_rec = Latest > OR dt_aud_rec = NoSoLatest > HAVING Latest > NoSoLatest > GROUP BY t1.id_secr_rqst > ORDER by t1.dt_aud_rec DESC; > > I am not 100% sure about the syntax, but you might get the idea. > > Peter Normann > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- Thanks and best regards, Anoop
