Anoop kumar V <[EMAIL PROTECTED]> wrote on 05/24/2005 03:02:11 PM: > 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 I would solve this query by first constructing a table that contains the information I need to identify the two most recent records (tasks). SINCE YOU HAVE YET TO POST AN ACTUAL TABLE STRUCTURE (shame on you), I will be forced to make up nearly every part of my answer. And because you want it to be cross-database portable, I won't be able to use the group-wize autonumber trick. However this will use a technique twice like the group-wize-maximum technique (described here: http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html ) CREATE TEMPORARY TABLE tmpMaxDates ( task_id varchar(10) not null, task_date date not null, INDEX(task_id, task_date) ); INSERT tmpMax (task_id, task_date) SELECT task_ID, max(task_date) FROM tasktable GROUP BY task_ID; #now collect the max(PK) value for each task_id/task_date pair CREATE TEMPORARY TABLE tmpRecordsToProcess ( task_id varchar(10) not null, task_date date not null, task_pk int not null INDEX(task_pk) ); INSERT tmpRecordsToProcess rtp (task_id, task_date, task_pk) SELECT tt.task_id, tt.task_date, max(tt.pk) FROM tasktable tt INNER JOIN tmpMaxDates md on tt.task_id = md.task_id AND tt.task_date = md.task_date GROUP BY tt.task_id, tt.task_date; # now to get the second record back DELETE FROM tmpMaxDates; INSERT tmpMaxDates (task_id, task_date) SELECT tt.task_id, max(tt.task_date) FROM tasktable tt LEFT JOIN tmpRecordsToProcess rtp ON rtp.task_pk = tt.PK WHERE rtp.task_pk is null GROUP BY tt.task_id; INSERT tmpRecordsToProcess (task_id, task_date, task_pk) SELECT tt.task_id, tt.task_date, max(tt.pk) FROM tasktable tt INNER JOIN tmpMaxDates md on tt.task_id = md.task_id AND tt.task_date = md.task_date LEFT JOIN tmpRecordsToProcess rtp ON rtp.task_pk = tt.PK WHERE rtp.task_PK is null GROUP BY tt.task_id, tt.task_date; Now (assuming I am not too hosed-up today) you should be able to process against tmpRecordsToProcess (using the task_pk field) to limit your queries to just those PK values you have identified as being the two most recent for each task (assuming a higher PK value is more recent than a lower one for the same task/date pair). This would have been much easier to code if we had been able to use the group-wize auto-increment feature of MyISAM. You can repeat the last 3 statements as often as you wish in order to build a larger "most recent" list. I am sure that if I made any logical errors, fresher minds on the list will catch them as I am nearing the end of a rather long day and could have easily mis-typed something. To summarize: tmpRecordsToProcess should contain a list of the primary key values of the two "most recent" records for each task. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
