My profound apologies.... here is the table create structure. the biggest problem i think is that this table does not have any primary keys or atleast unique columns: (I think joins require unique columns)
mysql> show create table isr2_aud_log\G *************************** 1. row *************************** Table: isr2_aud_log Create Table: CREATE TABLE `isr2_aud_log` ( `id_secr_rqst` varchar(64) NOT NULL default '', `dt_aud_rec` datetime NOT NULL default '0000-00-00 00:00:00', `name_rec_type` varchar(30) default NULL, `cd_rqst_type` varchar(15) default NULL, `id_user` varchar(10) default NULL, `name_user_first` varchar(40) default NULL, `name_user_mid` varchar(40) default NULL, `name_user_lst` varchar(40) default NULL, `cd_user_div` varchar(10) default NULL, `cd_user_cst_cntr` varchar(15) default NULL, `id_actnee` varchar(10) default NULL, `name_actnee_first` varchar(40) default NULL, `name_actnee_mid` varchar(40) default NULL, `name_actnee_lst` varchar(40) default NULL, `cd_pltfrm` varchar(10) default NULL, `cd_rsrc_sub_type` varchar(10) default NULL, `cd_actn` varchar(10) default NULL, `cd_rsrc_div` varchar(10) default NULL, `name_grp` varchar(70) default NULL, `name_svr` varchar(70) default NULL, `name_rsrc_1` varchar(70) default NULL, `name_rsrc_2` varchar(70) default NULL, `name_rsrc_3` varchar(70) default NULL, `name_rsrc_4` varchar(70) default NULL, `name_rsrc_5` varchar(70) default NULL, `cd_sts_apprl` varchar(30) default NULL, `cd_prcsg_type` varchar(10) default NULL, `text_actnee_cmnts` varchar(255) default NULL, `text_spcl_instn` varchar(255) default NULL, `dt_lst_updt` datetime default NULL, `id_user_lst_updt` varchar(8) default NULL ) TYPE=MyISAM I did read your response/answer to my problem and being a newbie, I found it quite complicated for me to follow. Does it really require more than just 1 or 2 simple select queries to pull out rows ( unique id_secr_rqst - 2 of them for each) which have the max(dt_aud_rec) and second max(dt_aud_rec)? I mean simple queries with joins. I cannot use sub queries. I am assured that the table will have no more than a 1000 records and after my initial filtering I will have to deal with 100 records maximum. So performance is not a problem at all. If SGreen's response is the only one then its ok - I will try to follow that - else I think it has room for simplication a bit. Thanks, Anoop On 5/24/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > > 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 <http://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 <http://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 <http://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 <http://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 -- Thanks and best regards, Anoop
