Re: complicated query | no Sub query
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
Re: complicated query | no Sub query
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
Re: complicated query | no Sub query
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 '-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,
complicated query | no Sub query
hi All, I need to create a query using no subqueries as I use 4.0.23 which does not support subqueries. I cannot upgrade for some compellimg reasons (the product does not support anything later than 4.0.23nt as of now). I also cannot use any thing that is native to mysql - in the sense that the query should be as generic/simple as possible so that I can run it against both DB2 or Sybase. Here is the problem.. I have this table: (modified to simplify) ++ | id_secr| name_rec_type | dt_aud_rec | ++ | TASKD1 | Risk Assessment | 2005-05-20 19:07:54 | | TASKD1 | Assigned | 2005-05-20 19:07:53 | | TASKD1 | Pending | 2005-05-20 12:10:50 | | TASKD2 | Closed | 2005-05-20 19:06:27 | | TASKD2 | Risk Assessment | 2005-05-20 19:06:04 | | TASKD2 | Pending | 2005-05-20 19:05:54 | | TASKD3 | Closed | 2005-05-20 16:40:14 | | TASKD3 | Risk Assessment | 2005-05-20 10:07:54 | | TASKD3 | Assigned | 2005-05-20 10:00:54 | | TASKD4 | Closed | 2005-05-20 10:34:13 | | TASKD4 | Risk Assessment | 2005-05-20 09:07:54 | | TASKD4 | Assigned | 2005-05-20 09:00:54 | | TASKD4 | Assigned | 2005-05-20 09:00:04 | | TASKD5 | Closed | 2005-05-20 15:33:13 | | TASKD5 | SERB Assessment | 2005-05-20 15:07:54 | | TASKD5 | Assigned | 2005-05-20 14:07:54 | | TASKD5 | Risk Assessment | 2005-05-20 13:07:54 | | TASKD5 | Risk Assessment | 2005-05-20 12:07:54 | | TASKD6 | Closed | 2005-05-20 14:18:28 | | TASKD6 | Risk Assessment | 2005-05-20 13:07:54 | | TASKD6 | Assigned | 2005-05-20 12:07:54 | | TASKD6 | Pending | 2005-05-20 11:07:54 | | TASKD6 | Pending | 2005-05-20 10:07:54 | | TASKD6 | Pending | 2005-05-20 09:07:54 | | TASKD6 | Pending | 2005-05-20 08:07:54 | | TASKD6 | Pending | 2005-05-20 07:07:54 | ++ what I need is to pull out data based on the latest two dates. I will be checking the status (name_rec_type) and if my status matches any one of the latest 2 name_rec_type I will do some processing. The only thing compounding this is that I cannot use any sub queries - I can do as many joins as necessary. Also the query need not be performance intensive as I dont think we will have more than 2000 rows at any time. Moreover I will have to ignore all rows (or wholes TASKD*'s) where the name_rec_type is closed anywhere. here is what I have come up so far - but it gives only the latest data: (and not the last 2 latest) select t1.id_secr_rqst, t2.name_rec_type, max(t1.dt_aud_rec) 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' group by t1.id_secr_rqst I need help. Thanks in advance. Anoop
RE: complicated query | no Sub query
Hi Anoop Try: SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec 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' ORDER by t1.dt_aud_rec DESC LIMIT 2; Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: complicated query | no Sub query
Thanks Peter - but I see two issues: 1. It returns data about only one id_secr_rqst - I want it to return data about every id_secr_rqst in the table. 2. Limit IMO is mysql specific (I hope I am wrong) is there something generic so I dont need to bother about which database I am running it against. Thanks, Anoop On 5/23/05, Peter Normann [EMAIL PROTECTED] wrote: Hi Anoop Try: SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec 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' ORDER by t1.dt_aud_rec DESC LIMIT 2; 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
RE: complicated query | no Sub query
Anoop kumar V mailto:[EMAIL PROTECTED] wrote: 1. It returns data about only one id_secr_rqst - I want it to return data about every id_secr_rqst in the table. So, if I understand you correctly (sorry, having a bad day), you want all records for the past two days? Assuming this, you could use something like: SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec 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 CURDATE() - 2; ORDER by t1.dt_aud_rec DESC; 2. Limit IMO is mysql specific (I hope I am wrong) is there something generic so I dont need to bother about which database I am running it against. As far as I know it is MySql specific. SQL Server's equivalent is SELECT TOP 2 or something. Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: complicated query | no Sub query
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]
