case select
Hi all, I have case like this. 20031221175014.65 0 14.65 14.65 6 200312211800118.9 119.41 14.65 697.17 2003122118101324.1 122.6 697.17 1502.3 2 Last field is MODE. That case is at the middle of the database. But these 3 are continues tripel. When MODE='' I need to know all the values of the previous and the next rows. Is there a SQL statement I can use, or do I need to write a code? Regards, Ferhat Is there a way to do that -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need a query to get the difference of two tables
SELECT a.address FROM a, b WHERE (a.id LIKE b.id) AND (b.message NOT LIKE 'y') -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Sunday, January 30, 2005 7:19 PM To: mysql Cc: Harish Subject: Re: Need a query to get the difference of two tables Harish wrote: Hi, I apprecaite anybody replying me with an equvalent query for this: I am using mysql 4.0.21 select a.address from a where a.id not in (select b.iid from b where b.message='y') This can be done with a left join: select a.address from a left join b on b.iid=a.id where b.iid is null; -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ALTERing a table with a CREATE TABLE statement????
Hi, I wrote a software generating a statistical database. That database keeps time series. What I am doing is - looking at the data file and get the channel names. - CREATE TABLE IF NOT EXISTS IF Database exits I want to ALTER the table but only add new coloums that are new. Is there a way to alter table with a create statement. What I mean is I will again generate a CREATE TABLE structure and than check the existing table if the coloums are match and add the new ones at once. Maybe there is such a command in mysql like ALTER TABLE table_name1 with CREATE TABLE table_name2; is there? if ot what do you advice me to do, in an easiest way? Regards... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MatLab connector
http://www.mmf.utoronto.ca/resrchres/mysql/ I use this.. it works well.. Ferhat - Original Message - From: Jose Antonio [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, November 25, 2004 4:57 PM Subject: MatLab connector Do you know if I can use MySQL data from MatLab? Does it exists a connector for MatLab in the same sense as JDBC for Java? Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
VS.NET C++ and MySQL
Hi, I know this is not the exact topic of the list but I am sure some people from this list spend some time on it. I am new to VC++ but getting better indeed. I need help on how to connect to MySQL server. I need a connector and an example. It must be free because this is a student project :) So far, I have tried http://myweb.hinet.net/home4/s630417/ (Code Lib .NET) but it only supports 4.1 or above. I do not want to upgrade and you all know why. Here is my box info: - Microsoft Development Enviroment 2003 version 7.1.3088 - Microsoft.NET Framework 1.1 version 1.1.4322 SP1 - Windows 2000 Professional - MySQL v4.0.22 - MyODBC 3.51.10 (which is not working very well I do not know why so I prefer File DSN if ODBC is needed. If any body point me to a free tool or an example that would be very nice. Regards... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Client program
Hi, I use those 2... MySQL Control Center http://www.mysql.com/products/mysqlcc/ PHPMyAdmin http://www.phpmyadmin.net/home_page/ Cheers... - Original Message - From: Scott Hamm [EMAIL PROTECTED] To: 'Mysql ' (E-mail) [EMAIL PROTECTED] Sent: Wednesday, November 24, 2004 2:38 PM Subject: Client program What is the best client program that I can use to get connected to MySQL remotely? I'm running Windows 2000... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Client program
http://dev.mysql.com/doc/administrator/en/mysql-administrator-menus-tools-mysql-command-line-client.html this may cheer you up than... - Original Message - From: Scott Hamm [EMAIL PROTECTED] To: 'Ferhat BINGOL' [EMAIL PROTECTED]; Scott Hamm [EMAIL PROTECTED]; 'Mysql ' (E-mail) [EMAIL PROTECTED] Sent: Wednesday, November 24, 2004 2:46 PM Subject: RE: Client program Isn't there any text-based program like telnet.exe or putty.exe that allows me to use similiar to 'cmd'? -Original Message- From: Ferhat BINGOL [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 24, 2004 8:47 AM To: Scott Hamm; 'Mysql ' (E-mail) Subject: Re: Client program Hi, I use those 2... MySQL Control Center http://www.mysql.com/products/mysqlcc/ PHPMyAdmin http://www.phpmyadmin.net/home_page/ Cheers... - Original Message - From: Scott Hamm [EMAIL PROTECTED] To: 'Mysql ' (E-mail) [EMAIL PROTECTED] Sent: Wednesday, November 24, 2004 2:38 PM Subject: Client program What is the best client program that I can use to get connected to MySQL remotely? I'm running Windows 2000... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Client program
just to save your time.. C:root/to/mysql/dir/bin/mysql.exe -h HOST_NAME_HERE -u USERNAME -p you will be promt for password. - Original Message - From: Scott Hamm [EMAIL PROTECTED] To: 'Ferhat BINGOL' [EMAIL PROTECTED]; Scott Hamm [EMAIL PROTECTED]; 'Mysql ' (E-mail) [EMAIL PROTECTED] Sent: Wednesday, November 24, 2004 2:46 PM Subject: RE: Client program Isn't there any text-based program like telnet.exe or putty.exe that allows me to use similiar to 'cmd'? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[solved] Re: LOAD DATA INFILE question...
Hi Patrick, I was doing that it was taking 20 seconds to chop the fields from the file. That was the problem. Meanwhile about my problem I have seen that the lines in data files end with \r\n not \n so it solved the problem. Now it takes 3 seconds to dump the file and chop the necassary fields. thanks to MySQL. :) thank yo again for answer... - Original Message - From: Patrick Connolly [EMAIL PROTECTED] To: Ferhat BINGOL [EMAIL PROTECTED] Cc: mysql [EMAIL PROTECTED] Sent: Sunday, November 21, 2004 10:00 AM Subject: Re: LOAD DATA INFILE question... Somewhere about Sat, 20-Nov-2004 at 06:27PM +0100 (give or take), Ferhat BINGOL wrote: | Hi, | | I have a 72 fields data txt file and I was inserting all data | previously but now I need only some of them to dump into the table. | I would like to select only 4 fields which are the 1st, 5th,28th | and 71st fields. | Is there a statement to do that. | I think it would be simpler to pre-process the file using cut with the appropriate delmiter if it's not tab-delimited already. Then import the reduced file. HTH -- ___ Patrick Connolly {~._.~} _( Y )_ Good judgment comes from experience (:_~*~_:) Experience comes from bad judgment (_)-(_) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA INFILE question...
Hi, I have a 72 fields data txt file and I was inserting all data previously but now I need only some of them to dump into the table. I would like to select only 4 fields which are the 1st, 5th,28th and 71st fields. Is there a statement to do that. I have read http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html but could not find it... Thanks...
Re: LOAD DATA INFILE question...
what about the file size. My file size is 10 mb. When I dump them all with LOAD DATA INFILE I only get first 9800 raws which is size of 2.9 mb. Is there a limit for file size? Where can I increase it? Regards.. - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: Ferhat BINGOL [EMAIL PROTECTED] Cc: mysql [EMAIL PROTECTED] Sent: Saturday, November 20, 2004 10:41 PM Subject: Re: LOAD DATA INFILE question... You can load the file to an intermediate table and then complete your process using INSERT INTO targetTable SELECT col_1, col_5, col_28, col_71 FROM intermediateTABLE Ferhat BINGOL wrote: Hi, I have a 72 fields data txt file and I was inserting all data previously but now I need only some of them to dump into the table. I would like to select only 4 fields which are the 1st, 5th,28th and 71st fields. Is there a statement to do that. I have read http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html but could not find it... Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL functions
Hi, How do I add a new function to MySQL SQL statement list. What I mean is AVG(), MIN() or MAX() is a ready function isnt it? Is there a way to add new functions without compiling all server? Regards... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT... INTO OUTFILE problem
The directory is being created by Labview and data is written out by MySQL. Can it be this? I am using windows 2000. And that reminded me, do I have to create the directory that I will output the file? Or, MySQL will automaticly create it if not exits??? Regards.. - Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: Mysql General (E-mail) [EMAIL PROTECTED] Sent: Thursday, October 21, 2004 10:35 PM Subject: RE: SELECT... INTO OUTFILE problem [snip] Exception occured in Microsoft OLE DB Provider for ODBC Drivers, [MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-nt]Can't create/write to file 'C:\data_out\day234\data_out.txt' (Errcode: 2) So I copy the query statement and send via PhpMyAdmin, it ended up as below #1 - Can't create/write to file 'C:\data_out\day234\data_out.txt' (Errcode: 2) So it is not a problem of Labview BUT my SQL statement... [/snip] It is a permissions problem, make sure that directory is writable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date as Primary ID
Hi Scoot, I do my table structure like that CREATE TABLE `test_table` ( `timestamp` date NOT NULL default '-00-00', `data` varchar(5) NOT NULL default '', PRIMARY KEY (`timestamp`), KEY `timestamp` (`timestamp`) ) TYPE=MyISAM; Than I send a query as below INSERT INTO `test_table` ( `timestamp` , `data` ) VALUES ( CURDATE( ) , 'test1' ); and this is the result SQL-query: SELECT * FROM `test_table` LIMIT 0, 30; Rows: 1 timestamp data 2004-10-18test1 Cheers, Ferhat - Original Message - From: Scott Hamm [EMAIL PROTECTED] To: 'Mysql ' (E-mail) [EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:20 PM Subject: Date as Primary ID How do I create table that uses timestamp in -dd-mm format as primary id (no duplicates)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a puzzle (at least for me)
Hi, I could not do this... TABLE1 ID NAME price - -- 1 1stname 34 2 2ndname 0 TABLE2 date 1stname 2ndname --- --- --- 20041023 23.5 12.3 As a result I want to get out raws something like this... 1 34 1stname 23.5 2 0 2ndname 12.3 What is the SQL syntax for this puzzle...?? Or is it possible? cheers... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
big table, slow queries...???
Hi, I am new at this group. Nowadays I am using PHP/MySQL more than ever. I have a problem with making queries on my huge database (totally more than 20 million rows and 9 GB). The main problem is it is rather slow. For example I am making a query which is checking the 5 biggest tables for, from 2 where options to 20. I am using something like that; SELECT table1.field1, table1.field2, table1.field3, table2.field2, table3.field2, table4.field1, table5.field1, table5.field2 FROM table1, table2, table3, table4, table5 WHERE (table1.field1=table2.field1) AND (table3.field4 BETWEEN case1 AND case2) (Where statement is longer than this) and so on. I think you understand the structure I use for making queries. But it is too slow. I mean I was expecting to be faster. Can you advice me a different kind of query string? I read some articles about using JOIN, GROUP or so on. But I am little bit confused about it. What is the best way to make queries on big table? Thanks, FERHAT P.s : I am using MySQL 3.23 and PHP/Apache on a Windows XP machine. 386 MB RAM with Pentium3 350. Ferhat - 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: big table, slow queries...???
Tore, Yes, it is necasarry to pick up all the values from 5 tables. I made indexes for each table. My queries are between 12 to 26 seconds now. I am generating a WHERE statement before make the query. If the user do not enter the some min and max values I am skipping this WHERE statements. The interesting thing is (for me) when I enter lots of limits for lots of input values query is faster. When I just select a country for example the query is slower. I mean lets say that speed field is between 10 and 40 and I want to chose a country. if I enter 10 and 40 for range and chose country it takes 12 seconds but if I do not enter speed range (as you see to enter or not is getting the same result) and choose a country it is 18 seconds. The main reason I asked this to the group is the same query was faster on interbase server. Than a question come to my mind is interbase server is faster in such huge queries? if so WHY? Cause on MySQL the simple queries are faster. As I said I am using MySQL 3.23 PHP Apache on WinXP. Does it help if I upgrade to MySQL 4.0 or is this come from XP... I am just trying to figure out if it is (much much) better to use Linux? TIA - 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