Re: query of a query?
Not necessarily. I would think the CREATE SELECT statement would be the closest equivalent. http://dev.mysql.com/doc/refman/4.1/en/create-table.html Thanks much for the reply, John. That surely would help with our future applications. But this time, we only have read access to the database. Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query of a query?
The following are Cold Fusion code. It's interesting that previously defined queries can be used as 'tables' to pull data from. In MySQL, does the same functionality have to be implemented using join or subselect? === CFQUERY name=getprojects datasource=#dbname# SELECT db_entry_num, title FROM account_info /CFQUERY CFQUERY name=getprojectID datasource=#GSN# SELECT projectID FROM ResearchProjects WHERE IDNo = #url.IDNo# /cfquery cfif getprojectID.recordcount GT 0 !--- Query of a query --- cfquery dbtype=query name=getstudproj SELECT title, db_entry_num, projectID FROM getprojects, getprojectID WHEREdb_entry_num = projectID /cfquery /cfif = Thanks, Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query of a query?
Bing Du wrote: The following are Cold Fusion code. It's interesting that previously defined queries can be used as 'tables' to pull data from. === CFQUERY name=getprojects datasource=#dbname# SELECT db_entry_num, title FROM account_info /CFQUERY CFQUERY name=getprojectID datasource=#GSN# SELECT projectID FROM ResearchProjects WHERE IDNo = #url.IDNo# /cfquery cfif getprojectID.recordcount GT 0 !--- Query of a query --- cfquery dbtype=query name=getstudproj SELECT title, db_entry_num, projectID FROM getprojects, getprojectID WHEREdb_entry_num = projectID /cfquery /cfif = It's interesting that previously defined queries can be used as 'tables' to pull data from. In MySQL, does the same functionality have to be implemented using join or subselect? Not necessarily. I would think the CREATE SELECT statement would be the closest equivalent. http://dev.mysql.com/doc/refman/4.1/en/create-table.html --J -- 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 - 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]
Newbie question: Create table from query or export query
Hi Is it possible to create a new table from the results of a query, or export the result directly to a file (txt, csv, excel or alike) using MySQL? How is it done? Thanks Renger _ ECOPLAN Forschung und Beratung in Wirtschaft und Politik Economic Research and Policy Consultancy Thunstrasse 22 / CH-3005 Berne (Switzerland) Phone: +41 31 356 61 61 / Fax: +41 31 356 61 60 mailto:[EMAIL PROTECTED] / http://www.ecoplan.ch - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbie question: Create table from query or export query
Yes. Assuming you are comfortable using the mysql client program you could use: CREATE TABLE newtablename SELECT * FROM existingtablename; In addition, if you have an existing table and need to load data from a text file you could use: LOAD DATA INFILE 'textfilename.txt' INTO TABLE existingtablename; There are many variations on these themes, but this should get you pointed in the right direction. Hope it helps. Craig Ostrander At 06:38 AM 4/15/2002, you wrote: Hi Is it possible to create a new table from the results of a query, or export the result directly to a file (txt, csv, excel or alike) using MySQL? How is it done? Thanks Renger _ ECOPLAN Forschung und Beratung in Wirtschaft und Politik Economic Research and Policy Consultancy Thunstrasse 22 / CH-3005 Berne (Switzerland) Phone: +41 31 356 61 61 / Fax: +41 31 356 61 60 mailto:[EMAIL PROTECTED] / http://www.ecoplan.ch - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php === Craig Ostrander Blue Forest Software and Development Inc Database Design Consultants Worldwide MySQL // SAP DB // Oracle // Microsoft SQL Server // Microsoft Access Need help now? [EMAIL PROTECTED] or +1 530 326 5680 === spam filter: sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
