Re: count(*) send a wrong value
Hi, Thank you a lot for your answer. I checked very carefully. The structure of the table is (exported by phpMyAdmin): CREATE TABLE `tp_participation` ( `uid` int(11) NOT NULL default '0', `challenge_id` int(11) NOT NULL default '0', `response` text collate latin1_general_ci NOT NULL, `points` int(11) default NULL, UNIQUE KEY `tp_id_part` (`uid`,`challenge_id`), KEY `tp_idx_part_solution` (`challenge_id`,`response`(4)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; I removed the key on challenge_id as suggested in another post. I do the following queries with phpMyAdmin (no direct access to mysql client): select * from tp_participation where challenge_id=20 10 records. select challenge_id from tp_participation where challenge_id=20 _two_ records with the value 20. select count(challenge_id) from tp_participation where challenge_id=20 One record with value 2 select count(*) from tp_participation where challenge_id=20 One record with value 2 select count(uid) from tp_participation where challenge_id=20 One record with value 10 select count(response) from tp_participation where challenge_id=20 One record with value 10 select count(points) from tp_participation where challenge_id=20 One record with value select count(response) from tp_participation where challenge_id=20 One record with value 0 (value of points is null for each field with challenge_id in the database, so it seems normal) Fabien Le lundi 23 janvier 2006 à 17:50 -0500, Rhino a écrit : What you're describing definitely sounds like a bug to me, assuming that you are accurately reporting the query you've used and the data in your table. In other words, if there really are 10 rows that have a cid value of 123 and you really are doing select * from table where cid = 123, then you should definitely be getting a result of 10, not 2. But that is a VERY strange error to be having! I've been writing SQL for over 20 years on a variety of platforms and I can't remember EVER seeing a count(*) give the wrong result. Any time the result was not what I expected, it turned out that I'd written the query incorrectly or I was wrong about what data was in the table. I'd also expect that the MySQL testing team would have executed many tests to be sure that basic functionality like count(*) works before ever releasing the product. Please, retest everything VERY carefully once more and make VERY sure that you aren't inadvertently writing the query incorrectly and that you really DO have 10 rows with cid = 123. If you still get 2 as the result of your query, I would recommend sending a bug report to MySQL. Rhino - Original Message - From: fabsk [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, January 23, 2006 5:32 PM Subject: Re: count(*) send a wrong value Thank you for you answer, but I read many times and I did not found something to answer my question (well, I did not know about the NULL). In my case: - there is one table - htere is no distinct - there is a WHERE clause, so there is no optimisation - there is no other field and no group by If I do count(cid), I still get 2. Fabien Le lundi 23 janvier 2006 à 20:54 +, [EMAIL PROTECTED] a écrit : From the MySQL 4.1 manual 12.10.1. GROUP BY (Aggregate) Functions COUNT(expr) Returns a count of the number of non-NULL values in the rows retrieved by a SELECT statement. COUNT() returns 0 if there were no matching rows. mysql SELECT student.student_name,COUNT(*) -FROM student,course -WHERE student.student_id=course.student_id -GROUP BY student_name; COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values. COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example: mysql SELECT COUNT(*) FROM student; This optimization applies only to MyISAM and ISAM tables only, because an exact record count is stored for these table types and can be accessed very quickly. For transactional storage engines (InnoDB, BDB), storing an exact row count is more problematic because multiple transactions may be occurring, each of which may affect the count. COUNT(DISTINCT expr,[expr...]) Returns a count of the number of different non-NULL values. COUNT(DISTINCT) returns 0 if there were no matching rows. mysql SELECT COUNT(DISTINCT results) FROM student; In MySQL, you can get the number of distinct expression combinations that do not contain NULL by giving a list of expressions. In standard SQL, you would have to do a concatenation of all expressions inside COUNT(DISTINCT ...). COUNT(DISTINCT ...) was added in MySQL 3.23.2. Keith In theory, theory and practice are the same;
Re: count(*) send a wrong value
Thank you a lot for your answer. I checked very carefully. The structure of the table is (exported by phpMyAdmin): CREATE TABLE `tp_participation` ( `uid` int(11) NOT NULL default '0', `challenge_id` int(11) NOT NULL default '0', `response` text collate latin1_general_ci NOT NULL, `points` int(11) default NULL, UNIQUE KEY `tp_id_part` (`uid`,`challenge_id`), KEY `tp_idx_part_solution` (`challenge_id`,`response`(4)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; I removed the key on challenge_id as suggested in another post. I do the following queries with phpMyAdmin (no direct access to mysql client): select * from tp_participation where challenge_id=20 10 records. select challenge_id from tp_participation where challenge_id=20 _two_ records with the value 20. select count(challenge_id) from tp_participation where challenge_id=20 One record with value 2 select count(*) from tp_participation where challenge_id=20 One record with value 2 select count(uid) from tp_participation where challenge_id=20 One record with value 10 select count(response) from tp_participation where challenge_id=20 One record with value 10 select count(points) from tp_participation where challenge_id=20 One record with value select count(response) from tp_participation where challenge_id=20 One record with value 0 (value of points is null for each field with challenge_id in the database, so it seems normal) What are the results if you drop all constraints and indices? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Google Against its Mission (Google Pack restriction for Iran!)
گوگل بر خلاف بیانیه ماموریت http://www.google.com/corporate/index.html خود سرویس گوگل پک را برای ایرانیان مسدود کرد. Google's mission is NOT to organize the world's information and make it universally accessible and useful anymore . http://pack.google.com
Re: Dump only data and Database
In article [EMAIL PROTECTED], Paul DuBois [EMAIL PROTECTED] writes: At 18:59 -0200 1/23/06, Luiz Rafael Culik Guimaraes wrote: Dear Friends What are the best options to dump an entire database on linux (with creation of databases and tables) with out dumping the index creation sentences What is an index creation sentence? Do you mean that you want the dump to include the CREATE TABLE statements, but for those statements not to contain the index definitions? If so, there's no option for that. I'd probably do it like that: * Dump structure and data separately * Weed out the index creation phrases from the structure dump by using sed/awk/perl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storing a string value with slash(\)
Hi, From the front end I need to use double slashes i.e (\\) to enter one slash (\) into the MySQL database. I.e, if I enter Gelb\Paha, it stores in the mySQL as GlebPaha, If I enter S\\Greeen, it stores as S\Green in the database. Is there any way in MySQL so that I can enter any number of slashes between the name with out escaping with another slash?. If I retrive the same value with the slash (\), not able to display properly in the GUI. Do we need to use any MySQL specific functions to select such values?. Please guide me for a solution here. Thanking you in advance. Thanks, Narasimha The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update query
Assume that you have two tables (in two different databases):
table A in database dbA:
id CHAR(6)
foo int
bar int
table B in database dbB:
id INT(6)
foo int
bar int
Both tables has a several records with identical ID values, but the format is
different ('001234' vs 1234).
Is it possible to create a update query that copies the 'foo' and 'bar' from
table dbA.A to dbB.B for each record in dbB.B?
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database backups
Hello. Have a look here: http://dev.mysql.com/doc/refman/5.0/en/backing-up.html George Law wrote: Just a quick question regarding mysql backups. I know myisam tables are portal from machine to machine, but I know there are some differences Between innodb tables. I am running mysql 5.0.18 on suse linux 10. I have innodb set up so it stores each table in its own .idb file. I've read that innodb tables are not portable from server to server, my question is if I grab the whole mysql/data directory, can it be restored back on the same computer in the event of a crash. Do I need to enable binlog to do this? I plan on giving myself about a 1 hour maintenance window where all my import scripts skip importing and then just copying the entire mysql/data directory to a back up server where I will tar/gzip the data and push it out to a back up directory so it will get dumped to tape. -- Geo -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Troubles installing MySQL5 via Darwin Ports
Hello. /opt/local/share/mysql5/mysql/mysql.server: line 234: cd: /opt/ local/libexec/mysqld: No such file or directory . • First of all, I check to see if 'mysqld' existed in /opt/local/ libexec/ and it does, so I don't know why it can't find it. Any ideas? Is /opt/local/libexec/mysqld a directory or a binary file? basedir system variable should point to the directory where MySQL Server is installed. Philip R. Thompson wrote: Hi all. Let me clarify my subject line. I *think* I actually got all the 'MySQL5 +server' files installed correctly using Darwin Ports. The problem is that I am not able to start my server. I think that 'my.cnf' may also be incorrect. Here's what's happening... - When I try to start the server --- [Claire:share/mysql5/mysql] chimi% sudo / /share/ mysql5/mysql/mysql.server start Password: /opt/local/share/mysql5/mysql/mysql.server: line 234: cd: /opt/ local/libexec/mysqld: No such file or directory Starting MySQL ERROR! Couldn't find MySQL manager or server [Claire:share/mysql5/mysql] chimi% my.cnf --- [mysqld] datadir=/opt/local/var/db/mysql5 socket=/tmp/mysql.sock port=3306 [mysql.server] user=mysql basedir=/opt/local/libexec/mysqld [client] socket=/opt/local/lib/mysql5/bin/mysql • First of all, I check to see if 'mysqld' existed in /opt/local/ libexec/ and it does, so I don't know why it can't find it. Any ideas? • Second what is wrong with my.cnf? I have searched the MySQL installation stuff and have not found anything that's useful. I have also searched archives on Darwin Ports and found some similar problems, but nothing that completely helps! Sorry for my *newbness*! Please help me! =D ~Philip -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I isolate the integer part of a varchar field and use it in an ORDER BY?
Hello. The brute force way is to create and UDF or store function which can extract the numeric part from the string and ORDER BY the results of this function. See: http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html http://dev.mysql.com/doc/refman/5.0/en/string-functions.html Nicolas Verhaeghe wrote: A client of mine sells motorcycle parts and the motorcycle models are for instance: YZ85 YZ125 WRF450 YZF450 Etc... If you know motorcycles, you know that the number is always the displacement in cc. What I am looking to do here is make it so that the models are sorted properly according to their displacement and not their alphanumerical order. Currently they are sorted like this: WRF450 YZ125 YZF450 YZ85 I would like them sorted like this: YZ85 YZ125 WRF450 YZF450 The displacement is not always at the end, sometimes it's at the beginning, for instance: 125SX 250EXC (Yes, those are Yamahas and KTMs, for those who are into that type of vehicles). How can I achieve this goal without creating a specific field in the database? I tried converting the field to integer, which is something that I can do with MS SQL (converting a varchar field to integer extracts the integer part, if any) but the CAST and CONVERT are not the same functions and I have looked for 30 minutes for something that could work with no success. Thanks a lot for your help! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 and PHP 4.4
Hello. From my experience PHP 4.4 works fine with MySQL 4.1. What troubles are you getting while restoring utf8 database to the server? Please, could you explain more in detail all steps of the restoring process. Include information about database character set as well. 立 周 wrote: Dear list subscribers, My web hosting server runs PHP 4.4 and MySQL 4.1. But PHP 4.4 doesn't fully support MySQL 4.1. and i have problems restoring a utf8 encoded database dumped from a MySQL 4.0 server to the new 4.1 server. Do i have any chance to get it working in this setup or should i persuade the hosting provider to either upgrade to PHP 5 so i can use Connection Character Sets and Collations in PHP or downgrade to MySQL 4.0? I am really stucked in this combination of PHP and MySQL and my site has been not working correctly for more than one month now. my site is at http://www.cnads.org/, i have manually changed all collation attribute to utf8_general_ci ( at database level, table level and column level) and it still doesn't work. Lionel . ___ 雅虎1G免费邮箱百分百防垃圾信 http://cn.mail.yahoo.com/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing a string value with slash(\)
Hello. If I retrive the same value with the slash (\), not able to display properly in the GUI. Do we need to use any MySQL specific functions to select such values?. If you're retrieving the same values which have stored, that this is rather a GUI problem, not MySQL. Is there any way in MySQL so that I can enter any number of slashes between the name with out escaping with another slash?. It is all depends on the way you're using to store data in MySQL. You can pass everything to mysql_real_escape_string(). See: http://dev.mysql.com/doc/refman/5.0/en/mysql-real-escape-string.html [EMAIL PROTECTED] wrote: Hi, From the front end I need to use double slashes i.e (\\) to enter one slash (\) into the MySQL database. I.e, if I enter Gelb\Paha, it stores in the mySQL as GlebPaha, If I enter S\\Greeen, it stores as S\Green in the database. Is there any way in MySQL so that I can enter any number of slashes between the name with out escaping with another slash?. If I retrive the same value with the slash (\), not able to display properly in the GUI. Do we need to use any MySQL specific functions to select such values?. Please guide me for a solution here. Thanking you in advance. Thanks, Narasimha The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update query
Hello.
If dbA.id has the format you have specified MySQL should be able to
silently convert the type from char to int, and you can work with dbA.id
as it is integer column.
mysql create table ch(id char(6));
Query OK, 0 rows affected (0.04 sec)
mysql insert into ch set id='001234';
Query OK, 1 row affected (0.00 sec)
mysql select id+0 from ch;
+--+
| id+0 |
+--+
| 1234 |
+--+
Use something similar to:
update dbB, dbA set dbB.foo=dbA.foo, dbB.bar=dbA.bar where
dbB.id=dbA.id ;
See:
http://dev.mysql.com/doc/refman/5.0/en/update.html
Jørn Dahl-Stamnes wrote:
Assume that you have two tables (in two different databases):
table A in database dbA:
idCHAR(6)
foo int
bar int
table B in database dbB:
idINT(6)
foo int
bar int
Both tables has a several records with identical ID values, but the format is
different ('001234' vs 1234).
Is it possible to create a update query that copies the 'foo' and 'bar' from
table dbA.A to dbB.B for each record in dbB.B?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update query
On Tuesday 24 January 2006 12:03, Gleb Paharenko wrote: Hello. If dbA.id has the format you have specified MySQL should be able to silently convert the type from char to int, and you can work with dbA.id as it is integer column. mysql create table ch(id char(6)); Query OK, 0 rows affected (0.04 sec) mysql insert into ch set id='001234'; Query OK, 1 row affected (0.00 sec) mysql select id+0 from ch; +--+ | id+0 | +--+ | 1234 | +--+ Use something similar to: update dbB, dbA set dbB.foo=dbA.foo, dbB.bar=dbA.bar where dbB.id=dbA.id ; See: http://dev.mysql.com/doc/refman/5.0/en/update.html Thanks a lot. That did the trick. I ended up with a command like this: update newdb.table as T,olddb.table as S set T.foo=S.foo,T.bar=S.bar,...(more fields that should be copied) where T.id=S.id; -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I isolate the integer part of a varchar field and use it in an ORDER BY?
Oh yeah, I forgot to tell... I still use 3.23. Cannot upgrade for the moment. So no SP... Is this possible at all with 3.23? Thanks for your help! -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 3:41 AM To: mysql@lists.mysql.com Subject: Re: How can I isolate the integer part of a varchar field and use it in an ORDER BY? Hello. The brute force way is to create and UDF or store function which can extract the numeric part from the string and ORDER BY the results of this function. See: http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html http://dev.mysql.com/doc/refman/5.0/en/string-functions.html Nicolas Verhaeghe wrote: A client of mine sells motorcycle parts and the motorcycle models are for instance: YZ85 YZ125 WRF450 YZF450 Etc... If you know motorcycles, you know that the number is always the displacement in cc. What I am looking to do here is make it so that the models are sorted properly according to their displacement and not their alphanumerical order. Currently they are sorted like this: WRF450 YZ125 YZF450 YZ85 I would like them sorted like this: YZ85 YZ125 WRF450 YZF450 The displacement is not always at the end, sometimes it's at the beginning, for instance: 125SX 250EXC (Yes, those are Yamahas and KTMs, for those who are into that type of vehicles). How can I achieve this goal without creating a specific field in the database? I tried converting the field to integer, which is something that I can do with MS SQL (converting a varchar field to integer extracts the integer part, if any) but the CAST and CONVERT are not the same functions and I have looked for 30 minutes for something that could work with no success. Thanks a lot for your help! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- 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: Troubles installing MySQL5 via Darwin Ports
On Jan 24, 2006, at 5:13 AM, James Harvard wrote: Is there a reason why you're using Darwin Ports and not the installer that MySQL offers? James Harvard Yes. I tried the installer multiple times and it would not work. I got so fed up with it that I decided to give DP a chance. So far, neither has impressed me - this just has to do with the installation, not the functionality of MySQL. I will truck on... ~Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I isolate the integer part of a varchar field and use it in an ORDER BY?
Nicolas, Not sure when the replace function was introduced into mysql, but I think it might do... Use replace in your order by, replacing a-z with null chars, leaving just your numeric digits, then order by select * from test; +-+---+ | uid | name | +-+---+ | 1 | george099 | | 2 | george100 | | 3 | george101 | | 4 | george001 | | 5 | 123bill | | 6 | 100bill | | 13 | george| | 14 | darren| | 15 | joe | | 16 | bill | +-+---+ 10 rows in set (0.00 sec) mysql select uid,name from test order by replace(name,'[a-z]',''); +-+---+ | uid | name | +-+---+ | 6 | 100bill | | 5 | 123bill | | 16 | bill | | 14 | darren| | 13 | george| | 4 | george001 | | 1 | george099 | | 2 | george100 | | 3 | george101 | | 15 | joe | +-+---+ You might need to convert 'name' to uppercase to work with all your part numbers. select uid,name from test order by replace(upper(name),'[A-Z]',''); -- George -Original Message- From: Nicolas Verhaeghe [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 9:13 AM To: 'Gleb Paharenko'; mysql@lists.mysql.com Subject: RE: How can I isolate the integer part of a varchar field and use it in an ORDER BY? Oh yeah, I forgot to tell... I still use 3.23. Cannot upgrade for the moment. So no SP... Is this possible at all with 3.23? Thanks for your help! -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 3:41 AM To: mysql@lists.mysql.com Subject: Re: How can I isolate the integer part of a varchar field and use it in an ORDER BY? Hello. The brute force way is to create and UDF or store function which can extract the numeric part from the string and ORDER BY the results of this function. See: http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html http://dev.mysql.com/doc/refman/5.0/en/string-functions.html Nicolas Verhaeghe wrote: A client of mine sells motorcycle parts and the motorcycle models are for instance: YZ85 YZ125 WRF450 YZF450 Etc... If you know motorcycles, you know that the number is always the displacement in cc. What I am looking to do here is make it so that the models are sorted properly according to their displacement and not their alphanumerical order. Currently they are sorted like this: WRF450 YZ125 YZF450 YZ85 I would like them sorted like this: YZ85 YZ125 WRF450 YZF450 The displacement is not always at the end, sometimes it's at the beginning, for instance: 125SX 250EXC (Yes, those are Yamahas and KTMs, for those who are into that type of vehicles). How can I achieve this goal without creating a specific field in the database? I tried converting the field to integer, which is something that I can do with MS SQL (converting a varchar field to integer extracts the integer part, if any) but the CAST and CONVERT are not the same functions and I have looked for 30 minutes for something that could work with no success. Thanks a lot for your help! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Truncated Incorrect Double Value
I am trying to create a query that will include HTML code for a link for a
datagrid that I've got. However, I'm getting the error, Truncated incorrect
DOUBLE value when I execute the following query:
SELECT *,
'a href=CamperSelItems.aspx?ID=' + CAST(ID AS Char) + 'Select Items/a' AS
ItemLink
FROM Campers
WHERE FamilyID=1
If I replace the CAST(ID AS Char) statement with 1, and remove the quotes
around it ('a href=CamperSelItems.aspx?ID=1Select Items/a') it works just
fine. So, I know that it's the ID field, and how I'm trying to get that to be
part of the link that it's got a problem with. So, what is the correct way to
do this?
Thanks,
Jesse
Re: Truncated Incorrect Double Value
Never mind,
I've found that if I use CONCAT(), it works just fine. It's hard for me to
get used to using CONCAT instead of just using the +. I've made this
mistake several times.
Thanks,
Jesse
- Original Message -
From: Jesse [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, January 24, 2006 10:23 AM
Subject: Truncated Incorrect Double Value
I am trying to create a query that will include HTML code for a link for a
datagrid that I've got. However, I'm getting the error, Truncated
incorrect DOUBLE value when I execute the following query:
SELECT *,
'a href=CamperSelItems.aspx?ID=' + CAST(ID AS Char) + 'Select Items/a'
AS ItemLink
FROM Campers
WHERE FamilyID=1
If I replace the CAST(ID AS Char) statement with 1, and remove the quotes
around it ('a href=CamperSelItems.aspx?ID=1Select Items/a') it works
just fine. So, I know that it's the ID field, and how I'm trying to get
that to be part of the link that it's got a problem with. So, what is the
correct way to do this?
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
回复： Re: MySQL 4.1 and PHP 4.4
--- Gleb Paharenko [EMAIL PROTECTED]写道: Hello. From my experience PHP 4.4 works fine with MySQL 4.1. What troubles are you getting while restoring utf8 database to the server? Please, could you explain more in detail all steps of the restoring process. Include information about database character set as well. I think the problem is: the server is running mysqld with latin chracter set and latin_swidish_ci collation ( the default ). But my database is utf8 encoded ( mainly Simplified Chinese ) with utf8_general_ci collation. PHP 4.4 doesn't provide API to work with Connection Character Sets and Collations. PHP 5 adds this and you can also compile PHP 4.4 with mysqli ( MySQL Improved ) extension to bring this API to PHP 4.4. But on a shared hosting plan, i obviously have no access to any of these solutions. Lionel ___ 雅虎1G免费邮箱百分百防垃圾信 http://cn.mail.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I isolate the integer part of a varchar field and use it in an ORDER BY?
Thanks, but unfortunately the replace function does not want to work on a regexp in version 3.23... I guess I'll have to create a displacement field and populate it from the admin tool. Thanks for your help. I will upgrade this server as soon as I can. -Original Message- From: George Law [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 8:14 AM To: Nicolas Verhaeghe; mysql@lists.mysql.com Subject: RE: How can I isolate the integer part of a varchar field and use it in an ORDER BY? Nicolas, Not sure when the replace function was introduced into mysql, but I think it might do... Use replace in your order by, replacing a-z with null chars, leaving just your numeric digits, then order by select * from test; +-+---+ | uid | name | +-+---+ | 1 | george099 | | 2 | george100 | | 3 | george101 | | 4 | george001 | | 5 | 123bill | | 6 | 100bill | | 13 | george| | 14 | darren| | 15 | joe | | 16 | bill | +-+---+ 10 rows in set (0.00 sec) mysql select uid,name from test order by replace(name,'[a-z]',''); +-+---+ | uid | name | +-+---+ | 6 | 100bill | | 5 | 123bill | | 16 | bill | | 14 | darren| | 13 | george| | 4 | george001 | | 1 | george099 | | 2 | george100 | | 3 | george101 | | 15 | joe | +-+---+ You might need to convert 'name' to uppercase to work with all your part numbers. select uid,name from test order by replace(upper(name),'[A-Z]',''); -- George -Original Message- From: Nicolas Verhaeghe [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 9:13 AM To: 'Gleb Paharenko'; mysql@lists.mysql.com Subject: RE: How can I isolate the integer part of a varchar field and use it in an ORDER BY? Oh yeah, I forgot to tell... I still use 3.23. Cannot upgrade for the moment. So no SP... Is this possible at all with 3.23? Thanks for your help! -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 3:41 AM To: mysql@lists.mysql.com Subject: Re: How can I isolate the integer part of a varchar field and use it in an ORDER BY? Hello. The brute force way is to create and UDF or store function which can extract the numeric part from the string and ORDER BY the results of this function. See: http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html http://dev.mysql.com/doc/refman/5.0/en/string-functions.html Nicolas Verhaeghe wrote: A client of mine sells motorcycle parts and the motorcycle models are for instance: YZ85 YZ125 WRF450 YZF450 Etc... If you know motorcycles, you know that the number is always the displacement in cc. What I am looking to do here is make it so that the models are sorted properly according to their displacement and not their alphanumerical order. Currently they are sorted like this: WRF450 YZ125 YZF450 YZ85 I would like them sorted like this: YZ85 YZ125 WRF450 YZF450 The displacement is not always at the end, sometimes it's at the beginning, for instance: 125SX 250EXC (Yes, those are Yamahas and KTMs, for those who are into that type of vehicles). How can I achieve this goal without creating a specific field in the database? I tried converting the field to integer, which is something that I can do with MS SQL (converting a varchar field to integer extracts the integer part, if any) but the CAST and CONVERT are not the same functions and I have looked for 30 minutes for something that could work with no success. Thanks a lot for your help! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- 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] -- 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: Troubles installing MySQL5 via Darwin Ports
I would think this list is more likely to be able to assist with problems using the standard installer from MySQL than with some third party installer / package management system. I would suggest using Disk Utility to repair permissions on your hard disc (just in case some relevant directory cannot be read/written to), then download the appropriate installer from mysql.com and let the list know what problems you encounter with that. Good luck, James Harvard On Jan 24, 2006, at 5:13 AM, James Harvard wrote: Is there a reason why you're using Darwin Ports and not the installer that MySQL offers? Yes. I tried the installer multiple times and it would not work. I got so fed up with it that I decided to give DP a chance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I isolate the integer part of a varchar field and use it in an ORDER BY?
Nicolas, What about just doing your sorting within your code instead of with mysql? Depending on how many rows you have that you would need to sort, it should not be too difficult to build a multidimensional array (add 2 columns, one with the alphabetical part of your key below, the other with the numeric part), and sort based on these 2 column. -- George -Original Message- From: Nicolas Verhaeghe [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 11:09 AM To: mysql@lists.mysql.com Subject: RE: How can I isolate the integer part of a varchar field and use it in an ORDER BY? Thanks, but unfortunately the replace function does not want to work on a regexp in version 3.23... I guess I'll have to create a displacement field and populate it from the admin tool. Thanks for your help. I will upgrade this server as soon as I can. -Original Message- From: George Law [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 8:14 AM To: Nicolas Verhaeghe; mysql@lists.mysql.com Subject: RE: How can I isolate the integer part of a varchar field and use it in an ORDER BY? Nicolas, Not sure when the replace function was introduced into mysql, but I think it might do... Use replace in your order by, replacing a-z with null chars, leaving just your numeric digits, then order by select * from test; +-+---+ | uid | name | +-+---+ | 1 | george099 | | 2 | george100 | | 3 | george101 | | 4 | george001 | | 5 | 123bill | | 6 | 100bill | | 13 | george| | 14 | darren| | 15 | joe | | 16 | bill | +-+---+ 10 rows in set (0.00 sec) mysql select uid,name from test order by replace(name,'[a-z]',''); +-+---+ | uid | name | +-+---+ | 6 | 100bill | | 5 | 123bill | | 16 | bill | | 14 | darren| | 13 | george| | 4 | george001 | | 1 | george099 | | 2 | george100 | | 3 | george101 | | 15 | joe | +-+---+ You might need to convert 'name' to uppercase to work with all your part numbers. select uid,name from test order by replace(upper(name),'[A-Z]',''); -- George -Original Message- From: Nicolas Verhaeghe [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 9:13 AM To: 'Gleb Paharenko'; mysql@lists.mysql.com Subject: RE: How can I isolate the integer part of a varchar field and use it in an ORDER BY? Oh yeah, I forgot to tell... I still use 3.23. Cannot upgrade for the moment. So no SP... Is this possible at all with 3.23? Thanks for your help! -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 3:41 AM To: mysql@lists.mysql.com Subject: Re: How can I isolate the integer part of a varchar field and use it in an ORDER BY? Hello. The brute force way is to create and UDF or store function which can extract the numeric part from the string and ORDER BY the results of this function. See: http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html http://dev.mysql.com/doc/refman/5.0/en/string-functions.html Nicolas Verhaeghe wrote: A client of mine sells motorcycle parts and the motorcycle models are for instance: YZ85 YZ125 WRF450 YZF450 Etc... If you know motorcycles, you know that the number is always the displacement in cc. What I am looking to do here is make it so that the models are sorted properly according to their displacement and not their alphanumerical order. Currently they are sorted like this: WRF450 YZ125 YZF450 YZ85 I would like them sorted like this: YZ85 YZ125 WRF450 YZF450 The displacement is not always at the end, sometimes it's at the beginning, for instance: 125SX 250EXC (Yes, those are Yamahas and KTMs, for those who are into that type of vehicles). How can I achieve this goal without creating a specific field in the database? I tried converting the field to integer, which is something that I can do with MS SQL (converting a varchar field to integer extracts the integer part, if any) but the CAST and CONVERT are not the same functions and I have looked for 30 minutes for something that could work with no success. Thanks a lot for your help! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- 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] -- MySQL General Mailing List For list archives:
Query: Order for the Earliest Latest date
[GENERAL INFO] I have two tables I'm working with. One table (Sites) contains contact information for every customer site that we deal with. The other table (Incidents) contains all the support calls we've made. [QUERY] I'm trying to generate a list of sites that HAD a support incident within a known date range, and order them so that the site that has the OLDEST support call is FIRST in the list. I'm using: SELECT DISTINCT id_Site FROM Incident WHERE Time = $date1 AND Time = $date2 ORDER BY Time DESC Which gives me a list of sites that had a support incident between the dates, but doesn't really sort them correctly. It simply orders them by who had the earliest support call. I'm looking for the site who's LAST support call is the EARLIEST. [Incident TABLE] Field Type Null Default Links to Comments MIME id int(11) No Time int(11) No 0when call came in text/plain Description varchar(100) No brief description Notes text No operator notes id_Site int(11) No 0 site - id ... Thanks for any pointers. DanB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error 2016 and 2013
nativecode=2013 ** Lost connection to MySQL server during query I think 2016 is Lost database connection What I am doing is I have a PHP class object that calls a stored procedure. I don't think I'm hitting any timeouts because it happens in less than a second. So here is my stored procedure: create procedure getElement (IN n VARCHAR(255),IN ptime DOUBLE) BEGIN DECLARE mfid INT UNSIGNED; DECLARE pid INT UNSIGNED; DECLARE foffset INT UNSIGNED; DECLARE flength INT UNSIGNED; DECLARE vid INT UNSIGNED; DECLARE rfid INT UNSIGNED; DECLARE tpid INT UNSIGNED; DECLARE fdata BLOB; DECLARE fdata_tmp BLOB; DECLARE fdata_bigint BIGINT UNSIGNED; DECLARE fdata_signed INT; DECLARE fdata_unsigned INT UNSIGNED; DECLARE fdata_float DOUBLE; DECLARE data_type VARCHAR(20); DECLARE byte_order VARCHAR(20); DECLARE conv_param VARCHAR(255); SELECT major_frame_desc_id, parent_id, frame_offset, frame_length, version_id, top_level_parent_id FROM MajorFrameDescription WHERE name=n INTO mfid,pid,foffset,flength,vid,tpid; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=NormalizedType INTO data_type; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=ConvParams INTO conv_param; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=ByteOrder INTO byte_order; SELECT MAX(raw_major_frame_id) FROM RawMajorFrames WHERE major_frame_desc_id=tpid INTO rfid; IF rfid 0 THEN SELECT payload_time, SUBSTR(BINARY(frame_data), FLOOR(foffset/8)+1, CEIL((flength + (foffset %8 ))/8)) FROM RawMajorFrames WHERE raw_major_frame_id=rfid INTO ptime,fdata; call toBigInt(fdata,fdata_bigint); IF (foffset %8) 0 THEN SET @mask_off=foffset%8; call mask_data(fdata,@mask_off,fdata_bigint); END IF; IF (8-((flength+(foffset%8)) %8)) 0 THEN SELECT (fdata_bigint (8-((flength+(foffset%8)) %8))) INTO fdata_bigint; END IF; CASE data_type WHEN Float THEN call toFloat(fdata_bigint,fdata_float); IF(!ISNULL(conv_param)) THEN call polyConv(fdata_float,conv_param,fdata_float); END IF; SET @fdata_converted=fdata_float; WHEN Double THEN call toFloat(fdata_bigint,fdata_float); IF(!ISNULL(conv_param)) THEN call polyConv(fdata_float,conv_param,fdata_float); END IF; SET @fdata_converted=fdata_float; WHEN Signed THEN call toSigned(fdata_bigint,fdata_signed); SET @fdata_converted=fdata_signed; WHEN Unsigned THEN SET @fdata_converted=fdata_bigint; ELSE SET @fdata_converted=HEX(fdata); END CASE; call enumConv(fdata_bigint,mfid,@fdata_enum); IF(!ISNULL(@fdata_enum)) THEN SET @[EMAIL PROTECTED]; END IF; SELECT mfid AS major_frame_desc_id, nAS name, pid AS parent_id, tpid AS top_level_parent_id,
Re: count(*) send a wrong value
Le mardi 24 janvier 2006 à 09:19 +0100, Martijn Tonies a écrit : CREATE TABLE `tp_participation` ( `uid` int(11) NOT NULL default '0', `challenge_id` int(11) NOT NULL default '0', `response` text collate latin1_general_ci NOT NULL, `points` int(11) default NULL, UNIQUE KEY `tp_id_part` (`uid`,`challenge_id`), KEY `tp_idx_part_solution` (`challenge_id`,`response`(4)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; select * from tp_participation where challenge_id=20 10 records. select challenge_id from tp_participation where challenge_id=20 _two_ records with the value 20. What are the results if you drop all constraints and indices? Thank you a lot for your answer. The bug is there: if I drop the index 'tp_idx_part_solution', the result of the count is OK. I recreated this index and the cound drop to 2 again. Fabien -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: count(*) send a wrong value
Le mardi 24 janvier 2006 à 19:08 +0100, fabsk a écrit : Thank you a lot for your answer. The bug is there: if I drop the index 'tp_idx_part_solution', the result of the count is OK. I recreated this index and the cound drop to 2 again. It doesn't happen on version 4.1.12-Max on my machine (the version on my web provider is 4.1.15). So it seems that it could be an old bug (or maybe I am lucky on my machine). Fabien -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: count(*) send a wrong value
Le mardi 24 janvier 2006 à 19:23 +0100, Fabien SK a écrit : Le mardi 24 janvier 2006 à 19:08 +0100, fabsk a écrit : Thank you a lot for your answer. The bug is there: if I drop the index 'tp_idx_part_solution', the result of the count is OK. I recreated this index and the cound drop to 2 again. It doesn't happen on version 4.1.12-Max on my machine (the version on my web provider is 4.1.15). So it seems that it could be an old bug (or maybe I am lucky on my machine). If found that it is this bug: http://bugs.mysql.com/bug.php?id=14980 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Help
Hi, This is my current query which works in mysql 4, but not in 5. Its from mambo, but im trying to modify it because they don't officially support mysql5 yet. The original query: SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name, v.name AS author FROM mos_content AS c, mos_categories AS cc, mos_sections AS s LEFT JOIN mos_groups AS g ON g.id = c.access LEFT JOIN mos_users AS u ON u.id = c.checked_out LEFT JOIN mos_users AS v ON v.id = c.created_by LEFT JOIN mos_content_frontpage AS f ON f.content_id = c.id WHERE c.state = 0 AND c.catid=cc.id AND cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY cc.ordering, cc.title, c.ordering LIMIT 0,10 My modified version: SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name, v.name AS author FROM mos_content c, mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id = c.access) LEFT JOIN mos_users u ON (u.id = c.checked_out) LEFT JOIN mos_users v ON (v.id = c.created_by) LEFT JOIN mos_content_frontpage f ON (f.content_id = c.id) WHERE c.state = 0 AND c.catid=cc.id AND cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY cc.ordering, cc.title, c.ordering LIMIT 0,10; The error I get for both ones is: Unknown column 'c.access' in 'on clause' Thanks and sorry for the stupid question. Cheers Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query: Order for the Earliest Latest date
In addition to the id_Site, you also need to grab the MAX(Time) so you have something to sort by. This requires a little trick known as a groupwise maximum. See http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html for an explanation and some examples. Greg Fortune On Tuesday 24 January 2006 09:06, Dan Baker wrote: [GENERAL INFO] I have two tables I'm working with. One table (Sites) contains contact information for every customer site that we deal with. The other table (Incidents) contains all the support calls we've made. [QUERY] I'm trying to generate a list of sites that HAD a support incident within a known date range, and order them so that the site that has the OLDEST support call is FIRST in the list. I'm using: SELECT DISTINCT id_Site FROM Incident WHERE Time = $date1 AND Time = $date2 ORDER BY Time DESC Which gives me a list of sites that had a support incident between the dates, but doesn't really sort them correctly. It simply orders them by who had the earliest support call. I'm looking for the site who's LAST support call is the EARLIEST. [Incident TABLE] Field Type Null Default Links to Comments MIME id int(11) No Time int(11) No 0when call came in text/plain Description varchar(100) No brief description Notes text No operator notes id_Site int(11) No 0 site - id ... Thanks for any pointers. DanB pgpQ7novDk8tC.pgp Description: PGP signature
Re: Query Help
Ian Barnes wrote: Hi, This is my current query which works in mysql 4, but not in 5. Its from mambo, but im trying to modify it because they don't officially support mysql5 yet. The original query: SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name, v.name AS author FROM mos_content AS c, mos_categories AS cc, mos_sections AS s LEFT JOIN mos_groups AS g ON g.id = c.access LEFT JOIN mos_users AS u ON u.id = c.checked_out LEFT JOIN mos_users AS v ON v.id = c.created_by LEFT JOIN mos_content_frontpage AS f ON f.content_id = c.id WHERE c.state = 0 AND c.catid=cc.id AND cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY cc.ordering, cc.title, c.ordering LIMIT 0,10 My modified version: SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name, v.name AS author FROM mos_content c, mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id = c.access) LEFT JOIN mos_users u ON (u.id = c.checked_out) LEFT JOIN mos_users v ON (v.id = c.created_by) LEFT JOIN mos_content_frontpage f ON (f.content_id = c.id) WHERE c.state = 0 AND c.catid=cc.id AND cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY cc.ordering, cc.title, c.ordering LIMIT 0,10; The error I get for both ones is: Unknown column 'c.access' in 'on clause' Thanks and sorry for the stupid question. Cheers Ian Replace all your comma joins to INNER JOIN syntax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 5.0.18: Bind on unix socket: Permission denied
Yesterday I installed the XAMPP (used to be called LAMP) stack, which includes mysql 5.0.18, on a Fedora Core 4 system. This seems like the quickest way of getting version 5 running WITHOUT clashing at all with my current install. Everything went 100% smooth, and I was able to copy my 4.x myisam based schemas over to the new directory (/opt/lampp/var/mysql), browse the data with mysql's gui clients as well as with the phpMysql console. (I do NOT bring both up simultaneously, and have disabled the autostart for the old one.) I changed the own and grp on the /opt/lampp directroy structure, with the owner now being mysql with full rights. I do not know at which point this happened but I can't bring up the thing any longer. This is what I get: 060124 14:15:44 mysqld started 060124 14:15:44 [ERROR] Can't start server : Bind on unix socket: Permission denied 060124 14:15:44 [ERROR] Do you already have another mysqld server running on socket: /opt/lampp/var/mysql/mysql.sock ? 060124 14:15:44 [ERROR] Aborting 060124 14:15:44 [Note] /opt/lampp/sbin/mysqld: Shutdown complete 060124 14:15:44 mysqld ended Your help is appreciated. -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Help
Hi, It now looks like this and still doesn't work, complains about exactly the same thing. SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name, v.name AS author FROM mos_content c, mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id = c.access) INNER JOIN mos_users u ON (u.id = c.checked_out) INNER JOIN mos_users v ON (v.id = c.created_by) INNER JOIN mos_content_frontpage f ON (f.content_id = c.id) WHERE c.state = 0 AND c.catid=cc.id AND cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY cc.ordering, cc.title, c.ordering LIMIT 0,10; Thanks, Ian -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: 24 January 2006 09:50 PM To: Ian Barnes Cc: mysql@lists.mysql.com Subject: Re: Query Help Ian Barnes wrote: Hi, This is my current query which works in mysql 4, but not in 5. Its from mambo, but im trying to modify it because they don't officially support mysql5 yet. The original query: SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name, v.name AS author FROM mos_content AS c, mos_categories AS cc, mos_sections AS s LEFT JOIN mos_groups AS g ON g.id = c.access LEFT JOIN mos_users AS u ON u.id = c.checked_out LEFT JOIN mos_users AS v ON v.id = c.created_by LEFT JOIN mos_content_frontpage AS f ON f.content_id = c.id WHERE c.state = 0 AND c.catid=cc.id AND cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY cc.ordering, cc.title, c.ordering LIMIT 0,10 My modified version: SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name, v.name AS author FROM mos_content c, mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id = c.access) LEFT JOIN mos_users u ON (u.id = c.checked_out) LEFT JOIN mos_users v ON (v.id = c.created_by) LEFT JOIN mos_content_frontpage f ON (f.content_id = c.id) WHERE c.state = 0 AND c.catid=cc.id AND cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY cc.ordering, cc.title, c.ordering LIMIT 0,10; The error I get for both ones is: Unknown column 'c.access' in 'on clause' Thanks and sorry for the stupid question. Cheers Ian Replace all your comma joins to INNER JOIN syntax -- 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: UK Postcodes
Hi Mike, Sorry for the late reply. The company I work for has this very same problem -- we are a multi-national personal ad site, where members can search for other members close to them. The answer is, unfortunately, you have to acquire one database with postcodes, and another with longitudes and latitudes, and merge them together. We spent a lot of time finding that answer, and when we did, it wasn't cheap. Sorry for the bad news. -Sheeri Kritzer On 1/7/06, Mike Blezien [EMAIL PROTECTED] wrote: Hello, we are working with a database that stores UK postcodes, which are different then US zipcodes. I've found alot of information for working with zipcodes, locating closed distances within a zipcode range, but haven't found anything regarding working with UK type postcodes. Was hoping someone on the list may have worked with UK postcodes and may have some info on the best way to query these postcodes for locating closed location, distances,.etc? thx's -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- 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: Query Help
That's because you are still using comma joins! ... FROM mos_content c, one comma mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id = ^--- another comma c.access) INNER JOIN mos_users u ON (u.id = c.checked_out) INNER JOIN mos_users v ON (v.id = c.created_by) INNER JOIN mos_content_frontpage f ON (f.content_id = c.id) WHERE c.state = 0 AND c.catid=cc.id AND cc.section=s.id AND s.scope='content' AND c.sectionid='1' ... If you feel uncomfortable making an INNER JOIN with no restrictions (no ON clause) you can call it a CROSS JOIN instead. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ian Barnes [EMAIL PROTECTED] wrote on 01/24/2006 03:15:24 PM: Hi, It now looks like this and still doesn't work, complains about exactly the same thing. SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name, v.name AS author FROM mos_content c, mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id = c.access) INNER JOIN mos_users u ON (u.id = c.checked_out) INNER JOIN mos_users v ON (v.id = c.created_by) INNER JOIN mos_content_frontpage f ON (f.content_id = c.id) WHERE c.state = 0 AND c.catid=cc.id AND cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY cc.ordering, cc.title, c.ordering LIMIT 0,10; Thanks, Ian -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: 24 January 2006 09:50 PM To: Ian Barnes Cc: mysql@lists.mysql.com Subject: Re: Query Help Ian Barnes wrote: Hi, This is my current query which works in mysql 4, but not in 5. Its from mambo, but im trying to modify it because they don't officially support mysql5 yet. The original query: SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name, v.name AS author FROM mos_content AS c, mos_categories AS cc, mos_sections AS s LEFT JOIN mos_groups AS g ON g.id = c.access LEFT JOIN mos_users AS u ON u.id = c.checked_out LEFT JOIN mos_users AS v ON v.id = c.created_by LEFT JOIN mos_content_frontpage AS f ON f.content_id = c.id WHERE c.state = 0 AND c.catid=cc.id AND cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY cc.ordering, cc.title, c.ordering LIMIT 0,10 My modified version: SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name, v.name AS author FROM mos_content c, mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id = c.access) LEFT JOIN mos_users u ON (u.id = c.checked_out) LEFT JOIN mos_users v ON (v.id = c.created_by) LEFT JOIN mos_content_frontpage f ON (f.content_id = c.id) WHERE c.state = 0 AND c.catid=cc.id AND cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY cc.ordering, cc.title, c.ordering LIMIT 0,10; The error I get for both ones is: Unknown column 'c.access' in 'on clause' Thanks and sorry for the stupid question. Cheers Ian Replace all your comma joins to INNER JOIN syntax -- 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: UK Postcodes
You'd think that the people who run the post office in the UK - British Telecom?? - would have had a number of enquiries from people who wanted to match postal codes with latitude and longitude. That would tend to give them a natural incentive to provide such information, all nicely integrated, possibly for a fairly affordable price. Any idea what would prevent the post office from doing that? Rhino - Original Message - From: sheeri kritzer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Sent: Tuesday, January 24, 2006 3:31 PM Subject: Re: UK Postcodes Hi Mike, Sorry for the late reply. The company I work for has this very same problem -- we are a multi-national personal ad site, where members can search for other members close to them. The answer is, unfortunately, you have to acquire one database with postcodes, and another with longitudes and latitudes, and merge them together. We spent a lot of time finding that answer, and when we did, it wasn't cheap. Sorry for the bad news. -Sheeri Kritzer On 1/7/06, Mike Blezien [EMAIL PROTECTED] wrote: Hello, we are working with a database that stores UK postcodes, which are different then US zipcodes. I've found alot of information for working with zipcodes, locating closed distances within a zipcode range, but haven't found anything regarding working with UK type postcodes. Was hoping someone on the list may have worked with UK postcodes and may have some info on the best way to query these postcodes for locating closed location, distances,.etc? thx's -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- 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] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date: 23/01/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date: 23/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Longest substring match
Hi All, I am working with some call processing. I have one table with call detail records (cdrs) with a phone number in it : 0111234567890 I have another table with rates in it based on the dial number with the 011 stripped off. There may be unique rates for 1234 1235 1236 1237 1238 1239 1230 Right now, this processing is done by taking the first 8 digits of the dialed number, doing a query Select * from rates where code=12345678 And seeing if there is a match, then taking 7 digits, seeing if there is a match, etc There is a chance it could come down to 2 digits, so that could be 6 queries, per cdr Right now, as the rates for one code are found, they are loaded into an array in perl and the next time that code comes up, the array is first checked before it does any more queries. I was just wondering if anyone had a better solution to be able to find the longest sub string match right in SQL. Thanks!! George Law -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lost connection to MySQL server during query
I am getting this error when connecting to mysql with PHP: Lost connection to MySQL server during query This happens only when I use this procedure, but it doesn't necessarily fail when this procedure is called. The error will happen frequently, however it is not consistent. This is my first procedure I've written, so I'm sure I've done something wrong here. I assume the error message means I'm hitting some kind of timeout? Any ideas would be welcome. Thanks. create procedure getElement (IN n VARCHAR(255),IN ptime DOUBLE) BEGIN DECLARE mfid INT UNSIGNED; DECLARE pid INT UNSIGNED; DECLARE foffset INT UNSIGNED; DECLARE flength INT UNSIGNED; DECLARE vid INT UNSIGNED; DECLARE rfid INT UNSIGNED; DECLARE tpid INT UNSIGNED; DECLARE fdata BLOB; DECLARE fdata_tmp BLOB; DECLARE fdata_bigint BIGINT UNSIGNED; DECLARE fdata_signed INT; DECLARE fdata_unsigned INT UNSIGNED; DECLARE fdata_float DOUBLE; DECLARE data_type VARCHAR(20); DECLARE byte_order VARCHAR(20); DECLARE conv_param VARCHAR(255); SELECT major_frame_desc_id, parent_id, frame_offset, frame_length, version_id, top_level_parent_id FROM MajorFrameDescription WHERE name=n INTO mfid,pid,foffset,flength,vid,tpid; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=NormalizedType INTO data_type; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=ConvParams INTO conv_param; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=ByteOrder INTO byte_order; SELECT MAX(raw_major_frame_id) FROM RawMajorFrames WHERE major_frame_desc_id=tpid INTO rfid; IF rfid 0 THEN SELECT payload_time, SUBSTR(BINARY(frame_data), FLOOR(foffset/8)+1, CEIL((flength + (foffset %8 ))/8)) FROM RawMajorFrames WHERE raw_major_frame_id=rfid INTO ptime,fdata; call toBigInt(fdata,fdata_bigint); IF (foffset %8) 0 THEN SET @mask_off=foffset%8; call mask_data(fdata,@mask_off,fdata_bigint); END IF; IF (8-((flength+(foffset%8)) %8)) 0 THEN SELECT (fdata_bigint (8-((flength+(foffset%8)) %8))) INTO fdata_bigint; END IF; CASE data_type WHEN Float THEN call toFloat(fdata_bigint,fdata_float); IF(!ISNULL(conv_param)) THEN call polyConv(fdata_float,conv_param,fdata_float); END IF; SET @fdata_converted=fdata_float; WHEN Double THEN call toFloat(fdata_bigint,fdata_float); IF(!ISNULL(conv_param)) THEN call polyConv(fdata_float,conv_param,fdata_float); END IF; SET @fdata_converted=fdata_float; WHEN Signed THEN call toSigned(fdata_bigint,fdata_signed); SET @fdata_converted=fdata_signed; WHEN Unsigned THEN SET @fdata_converted=fdata_bigint; ELSE SET @fdata_converted=HEX(fdata); END CASE; call enumConv(fdata_bigint,mfid,@fdata_enum); IF(!ISNULL(@fdata_enum)) THEN SET @[EMAIL PROTECTED]; END IF; SELECT mfid AS major_frame_desc_id,
RE: Lost connection to MySQL server during query
David, Are you using persistent connections? Sounds like perhaps a persistent connection is timing out. Maybe a quick work around would be to call a check status routine (ie - do a show status), just to see if the connection is still there. If this fails, just do a mysql_connect... Before continuing. -- George -Original Message- From: David Godsey [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 4:09 PM To: mysql@lists.mysql.com Subject: Lost connection to MySQL server during query I am getting this error when connecting to mysql with PHP: Lost connection to MySQL server during query This happens only when I use this procedure, but it doesn't necessarily fail when this procedure is called. The error will happen frequently, however it is not consistent. This is my first procedure I've written, so I'm sure I've done something wrong here. I assume the error message means I'm hitting some kind of timeout? Any ideas would be welcome. Thanks. create procedure getElement (IN n VARCHAR(255),IN ptime DOUBLE) BEGIN DECLARE mfid INT UNSIGNED; DECLARE pid INT UNSIGNED; DECLARE foffset INT UNSIGNED; DECLARE flength INT UNSIGNED; DECLARE vid INT UNSIGNED; DECLARE rfid INT UNSIGNED; DECLARE tpid INT UNSIGNED; DECLARE fdata BLOB; DECLARE fdata_tmp BLOB; DECLARE fdata_bigint BIGINT UNSIGNED; DECLARE fdata_signed INT; DECLARE fdata_unsigned INT UNSIGNED; DECLARE fdata_float DOUBLE; DECLARE data_type VARCHAR(20); DECLARE byte_order VARCHAR(20); DECLARE conv_param VARCHAR(255); SELECT major_frame_desc_id, parent_id, frame_offset, frame_length, version_id, top_level_parent_id FROM MajorFrameDescription WHERE name=n INTO mfid,pid,foffset,flength,vid,tpid; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=NormalizedType INTO data_type; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=ConvParams INTO conv_param; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=ByteOrder INTO byte_order; SELECT MAX(raw_major_frame_id) FROM RawMajorFrames WHERE major_frame_desc_id=tpid INTO rfid; IF rfid 0 THEN SELECT payload_time, SUBSTR(BINARY(frame_data), FLOOR(foffset/8)+1, CEIL((flength + (foffset %8 ))/8)) FROM RawMajorFrames WHERE raw_major_frame_id=rfid INTO ptime,fdata; call toBigInt(fdata,fdata_bigint); IF (foffset %8) 0 THEN SET @mask_off=foffset%8; call mask_data(fdata,@mask_off,fdata_bigint); END IF; IF (8-((flength+(foffset%8)) %8)) 0 THEN SELECT (fdata_bigint (8-((flength+(foffset%8)) %8))) INTO fdata_bigint; END IF; CASE data_type WHEN Float THEN call toFloat(fdata_bigint,fdata_float); IF(!ISNULL(conv_param)) THEN call polyConv(fdata_float,conv_param,fdata_float); END IF; SET @fdata_converted=fdata_float; WHEN Double THEN call toFloat(fdata_bigint,fdata_float); IF(!ISNULL(conv_param)) THEN call polyConv(fdata_float,conv_param,fdata_float); END IF; SET @fdata_converted=fdata_float; WHEN Signed THEN call toSigned(fdata_bigint,fdata_signed); SET @fdata_converted=fdata_signed; WHEN Unsigned THEN
Re: UK Postcodes
It's the Royal Mail. Ordnance Survey, the government mapping agency for the UK, are in on it too. To read their web site (as I have done a couple of years ago, and just now too) you would think it had never occurred to them that people might want to deploy the data as part of a web site. It's all about licensing the data by the number of 'terminals'. Extraordinary. Their prices are fairly extraordinary too. I've always meant to write and complain to Royal Mail, Ordnance Survey, the RM's independent regulator and anyone else I could think of about this inflated, monopoly pricing which can only be hindering UK businesses from developing localised on-line services. /rant James Harvard At 4:00 pm -0500 24/1/06, Rhino wrote: You'd think that the people who run the post office in the UK - British Telecom?? - would have had a number of enquiries from people who wanted to match postal codes with latitude and longitude. That would tend to give them a natural incentive to provide such information, all nicely integrated, possibly for a fairly affordable price. Any idea what would prevent the post office from doing that? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
Ian, It now looks like this and still doesn't work, complains about exactly the same thing. SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name, v.name AS author FROM mos_content c, mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id = c.access) INNER JOIN mos_users u ON (u.id = c.checked_out) INNER JOIN mos_users v ON (v.id = c.created_by) INNER JOIN mos_content_frontpage f ON (f.content_id = c.id) WHERE c.state = 0 AND c.catid=cc.id AND cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY cc.ordering, cc.title, c.ordering LIMIT 0,10; Your query still has a comma join. PB - Ian Barnes wrote: Hi, It now looks like this and still doesn't work, complains about exactly the same thing. SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name, v.name AS author FROM mos_content c, mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id = c.access) INNER JOIN mos_users u ON (u.id = c.checked_out) INNER JOIN mos_users v ON (v.id = c.created_by) INNER JOIN mos_content_frontpage f ON (f.content_id = c.id) WHERE c.state = 0 AND c.catid=cc.id AND cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY cc.ordering, cc.title, c.ordering LIMIT 0,10; Thanks, Ian -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED]] Sent: 24 January 2006 09:50 PM To: Ian Barnes Cc: mysql@lists.mysql.com Subject: Re: Query Help Ian Barnes wrote: Hi, This is my current query which works in mysql 4, but not in 5. Its from mambo, but im trying to modify it because they don't officially support mysql5 yet. The original query: SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name, v.name AS author FROM mos_content AS c, mos_categories AS cc, mos_sections AS s LEFT JOIN mos_groups AS g ON g.id = c.access LEFT JOIN mos_users AS u ON u.id = c.checked_out LEFT JOIN mos_users AS v ON v.id = c.created_by LEFT JOIN mos_content_frontpage AS f ON f.content_id = c.id WHERE c.state = 0 AND c.catid=cc.id AND cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY cc.ordering, cc.title, c.ordering LIMIT 0,10 My modified version: SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name, v.name AS author FROM mos_content c, mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id = c.access) LEFT JOIN mos_users u ON (u.id = c.checked_out) LEFT JOIN mos_users v ON (v.id = c.created_by) LEFT JOIN mos_content_frontpage f ON (f.content_id = c.id) WHERE c.state = 0 AND c.catid=cc.id AND cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY cc.ordering, cc.title, c.ordering LIMIT 0,10; The error I get for both ones is: Unknown column 'c.access' in 'on clause' Thanks and sorry for the stupid question. Cheers Ian Replace all your comma joins to INNER JOIN syntax No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date: 1/23/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: key_buffer_size vs innodb_buffer_pool_size
innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size + read_buffer_size + binlog_cache_size) + max_connections*2MB is what I was told recently on this list. -Sheeri Kritzer On 1/16/06, Grant Giddens [EMAIL PROTECTED] wrote: I don't know if I still fully understand. Say I have a database with a good mix of myisam and innodb type tables. If my server has 1 gig of ram and I set key_buffer_size=256M and innodb_buffer_pool_size=256M then wouldn't mysql be constrained to 512M of the ram and leave the remaining 512M to the other server services? For a large database, are they any guides that I can use to determine how much ram would be optimal for my particular database? Thanks, Grant Eric Bergen [EMAIL PROTECTED] wrote: The difference in recommendation size comes from the different techniques each storage engine uses for caching data. myisam (key_buffer_size) only stores indexes where innodb_buffer_pool_size stores both indexes and data. mysiam relies on the operating system to cache data in ram which is why you don't want to use all available memory for the key buffer. On 1/14/06, Grant Giddens wrote: Hi, After reading through the example my.cnf files (large, huge, etc), I started to wonder what the difference was between the isam key_buffer_size and the innodb innodb_buffer_pool_size. I realize that they are two different table types, but some of the docs says to set the key_buffer_size to 25%-50% of the overall system memory. The comments for the innodb_buffer_pool_size say that it can be set to 50%-80% of the overall system memory. Maybe I don't understand exactly the difference between the two because I don't understand why they have different memory recommendations. Is there any FAQs on the my.cnf file? How would you set these two variables if you had an even mix of isam and innodb tables? Where can I learn more about tweaking the my.cnf file? The mysql online documentation is good, but I need a more basic description of these two variables and all the other my.cnf settings. Thanks, Grant - Yahoo! Photos Got holiday prints? See all the ways to get quality prints in your hands ASAP. -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Yahoo! Photos – Showcase holiday pictures in hardcover Photo Books. You design it and we'll bind it!
RE: Lost connection to MySQL server during query
Thanks for the reply. I'm not using persistend connections though. It appears that it looses the connection in the middle of the query or in other words, before the procedure returns. So that means I not getting the data I need. So for debug purposes, are you saying to do a check status from PHP or in the procedure? From the procedure it wouldn't do any good right? From PHP it would be after I didn't get the data, so I would have to reconnect and rerun the query. That won't really work for me either. David Godsey David, Are you using persistent connections? Sounds like perhaps a persistent connection is timing out. Maybe a quick work around would be to call a check status routine (ie - do a show status), just to see if the connection is still there. If this fails, just do a mysql_connect... Before continuing. -- George -Original Message- From: David Godsey [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 4:09 PM To: mysql@lists.mysql.com Subject: Lost connection to MySQL server during query I am getting this error when connecting to mysql with PHP: Lost connection to MySQL server during query This happens only when I use this procedure, but it doesn't necessarily fail when this procedure is called. The error will happen frequently, however it is not consistent. This is my first procedure I've written, so I'm sure I've done something wrong here. I assume the error message means I'm hitting some kind of timeout? Any ideas would be welcome. Thanks. create procedure getElement (IN n VARCHAR(255),IN ptime DOUBLE) BEGIN DECLARE mfid INT UNSIGNED; DECLARE pid INT UNSIGNED; DECLARE foffset INT UNSIGNED; DECLARE flength INT UNSIGNED; DECLARE vid INT UNSIGNED; DECLARE rfid INT UNSIGNED; DECLARE tpid INT UNSIGNED; DECLARE fdata BLOB; DECLARE fdata_tmp BLOB; DECLARE fdata_bigint BIGINT UNSIGNED; DECLARE fdata_signed INT; DECLARE fdata_unsigned INT UNSIGNED; DECLARE fdata_float DOUBLE; DECLARE data_type VARCHAR(20); DECLARE byte_order VARCHAR(20); DECLARE conv_param VARCHAR(255); SELECT major_frame_desc_id, parent_id, frame_offset, frame_length, version_id, top_level_parent_id FROM MajorFrameDescription WHERE name=n INTO mfid,pid,foffset,flength,vid,tpid; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=NormalizedType INTO data_type; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=ConvParams INTO conv_param; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=ByteOrder INTO byte_order; SELECT MAX(raw_major_frame_id) FROM RawMajorFrames WHERE major_frame_desc_id=tpid INTO rfid; IF rfid 0 THEN SELECT payload_time, SUBSTR(BINARY(frame_data), FLOOR(foffset/8)+1, CEIL((flength + (foffset %8 ))/8)) FROM RawMajorFrames WHERE raw_major_frame_id=rfid INTO ptime,fdata; call toBigInt(fdata,fdata_bigint); IF (foffset %8) 0 THEN SET @mask_off=foffset%8; call mask_data(fdata,@mask_off,fdata_bigint); END IF; IF (8-((flength+(foffset%8)) %8)) 0 THEN SELECT (fdata_bigint (8-((flength+(foffset%8)) %8))) INTO fdata_bigint; END IF; CASE data_type WHEN Float THEN call toFloat(fdata_bigint,fdata_float); IF(!ISNULL(conv_param)) THEN call polyConv(fdata_float,conv_param,fdata_float); END IF; SET @fdata_converted=fdata_float; WHEN Double THEN call toFloat(fdata_bigint,fdata_float); IF(!ISNULL(conv_param)) THEN
Re: Query: Order for the Earliest Latest date
Dan, I'm trying to generate a list of sites that HAD a support incident within a known date range, and order them so that the site that has the OLDEST support call is FIRST in the list. It's the (oft-asked-for) groupwise-max query. Here's one way, assuming you have MySQL 4.1 or later ... SELECT id_site, time AS 'Earliest Last Support' FROM incident AS i1 WHERE time = ( SELECT MAX( e2.time) FROM incident AS i2 WHERE i2.id_site = i1.id_site ) ORDER BY id_site; If your MySQL version is earlier than 4.1, change the subquery to a stage 1 query into a temp table then select order by from that. HTH. PB - Dan Baker wrote: [GENERAL INFO] I have two tables I'm working with. One table (Sites) contains contact information for every customer site that we deal with. The other table (Incidents) contains all the support calls we've made. [QUERY] I'm trying to generate a list of sites that HAD a support incident within a known date range, and order them so that the site that has the OLDEST support call is FIRST in the list. I'm using: SELECT DISTINCT id_Site FROM Incident WHERE Time = $date1 AND Time = $date2 ORDER BY Time DESC Which gives me a list of sites that had a support incident between the dates, but doesn't really sort them correctly. It simply orders them by who had the earliest support call. I'm looking for the site who's LAST support call is the EARLIEST. [Incident TABLE] Field Type Null Default Links to Comments MIME id int(11) No Time int(11) No 0when call came in text/plain Description varchar(100) No brief description Notes text No operator notes id_Site int(11) No 0 site - id ... Thanks for any pointers. DanB -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date: 1/23/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost connection to MySQL server during query
I got exactly that error message last night when doing a numeric comparison on a varchar column. Oops. Kind of misleading, though. - Original Message - From: David Godsey [EMAIL PROTECTED] To: George Law [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, January 24, 2006 4:43 PM Subject: RE: Lost connection to MySQL server during query Thanks for the reply. I'm not using persistend connections though. It appears that it looses the connection in the middle of the query or in other words, before the procedure returns. So that means I not getting the data I need. So for debug purposes, are you saying to do a check status from PHP or in the procedure? From the procedure it wouldn't do any good right? From PHP it would be after I didn't get the data, so I would have to reconnect and rerun the query. That won't really work for me either. David Godsey David, Are you using persistent connections? Sounds like perhaps a persistent connection is timing out. Maybe a quick work around would be to call a check status routine (ie - do a show status), just to see if the connection is still there. If this fails, just do a mysql_connect... Before continuing. -- George -Original Message- From: David Godsey [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 4:09 PM To: mysql@lists.mysql.com Subject: Lost connection to MySQL server during query I am getting this error when connecting to mysql with PHP: Lost connection to MySQL server during query This happens only when I use this procedure, but it doesn't necessarily fail when this procedure is called. The error will happen frequently, however it is not consistent. This is my first procedure I've written, so I'm sure I've done something wrong here. I assume the error message means I'm hitting some kind of timeout? Any ideas would be welcome. Thanks. create procedure getElement (IN n VARCHAR(255),IN ptime DOUBLE) BEGIN DECLARE mfid INT UNSIGNED; DECLARE pid INT UNSIGNED; DECLARE foffset INT UNSIGNED; DECLARE flength INT UNSIGNED; DECLARE vid INT UNSIGNED; DECLARE rfid INT UNSIGNED; DECLARE tpid INT UNSIGNED; DECLARE fdata BLOB; DECLARE fdata_tmp BLOB; DECLARE fdata_bigint BIGINT UNSIGNED; DECLARE fdata_signed INT; DECLARE fdata_unsigned INT UNSIGNED; DECLARE fdata_float DOUBLE; DECLARE data_type VARCHAR(20); DECLARE byte_order VARCHAR(20); DECLARE conv_param VARCHAR(255); SELECT major_frame_desc_id, parent_id, frame_offset, frame_length, version_id, top_level_parent_id FROM MajorFrameDescription WHERE name=n INTO mfid,pid,foffset,flength,vid,tpid; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=NormalizedType INTO data_type; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=ConvParams INTO conv_param; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=ByteOrder INTO byte_order; SELECT MAX(raw_major_frame_id) FROM RawMajorFrames WHERE major_frame_desc_id=tpid INTO rfid; IF rfid 0 THEN SELECT payload_time, SUBSTR(BINARY(frame_data), FLOOR(foffset/8)+1, CEIL((flength + (foffset %8 ))/8)) FROM RawMajorFrames WHERE raw_major_frame_id=rfid INTO ptime,fdata; call toBigInt(fdata,fdata_bigint); IF (foffset %8) 0 THEN SET @mask_off=foffset%8; call mask_data(fdata,@mask_off,fdata_bigint); END IF; IF (8-((flength+(foffset%8)) %8)) 0 THEN SELECT (fdata_bigint (8-((flength+(foffset%8)) %8))) INTO fdata_bigint; END IF; CASE data_type WHEN Float THEN call toFloat(fdata_bigint,fdata_float); IF(!ISNULL(conv_param)) THEN
Re: Query: Order for the Earliest Latest date
Peter Brawley [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Dan, I'm trying to generate a list of sites that HAD a support incident within a known date range, and order them so that the site that has the OLDEST support call is FIRST in the list. It's the (oft-asked-for) groupwise-max query. Here's one way, assuming you have MySQL 4.1 or later ... SELECT id_site, time AS 'Earliest Last Support' FROM incident AS i1 WHERE time = ( SELECT MAX( e2.time) FROM incident AS i2 WHERE i2.id_site = i1.id_site ) ORDER BY id_site; If your MySQL version is earlier than 4.1, change the subquery to a stage 1 query into a temp table then select order by from that. Bummer ... I'm running MySQL 4.0. I've never done a temp-table query. But, I'll give it a shot! Thanks DanB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I isolate the integer part of a varchar field and use it in an ORDER BY?
George Law wrote:
Nicolas,
Not sure when the replace function was introduced into mysql, but I
think it might do...
REPLACE() exists in 3.23.
Use replace in your order by, replacing a-z with null chars, leaving
just your numeric digits, then order by
Easier said than done.
select * from test;
+-+---+
| uid | name |
+-+---+
| 1 | george099 |
| 2 | george100 |
| 3 | george101 |
| 4 | george001 |
| 5 | 123bill |
| 6 | 100bill |
| 13 | george|
| 14 | darren|
| 15 | joe |
| 16 | bill |
+-+---+
10 rows in set (0.00 sec)
mysql select uid,name from test order by replace(name,'[a-z]','');
REPLACE doesn't accept patterns in the search string. This REPLACE is looking
for a literal occurrence of the string '[a-z]' to be replaced with ''.
+-+---+
| uid | name |
+-+---+
| 6 | 100bill |
| 5 | 123bill |
| 16 | bill |
| 14 | darren|
| 13 | george|
| 4 | george001 |
| 1 | george099 |
| 2 | george100 |
| 3 | george101 |
| 15 | joe |
+-+---+
Look again. Those are in alphabetical order, not numerical.
You might need to convert 'name' to uppercase to work with all your part
numbers.
select uid,name from test order by replace(upper(name),'[A-Z]','');
REPLACE is case-sensitive, but this method just won't work.
mysql SELECT REPLACE('123abcd45','[a-z]','');
+-+
| REPLACE('123abcd45','[a-z]','') |
+-+
| 123abcd45 |
+-+
1 row in set (0.11 sec)
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I isolate the integer part of a varchar field and use it in an ORDER BY?
Because I am currently stuck with 3.23 I have just decided to create a
displacement field to isolate the number. Besides, some of these bikes
escape from the rules, for instance instead of 600 for 600cc, you only
have a mere 6, 1 stands for 1000.
So all in all the displacement fields will work fine.
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 24, 2006 10:11 PM
To: George Law
Cc: Nicolas Verhaeghe; mysql@lists.mysql.com
Subject: Re: How can I isolate the integer part of a varchar field and
use it in an ORDER BY?
George Law wrote:
Nicolas,
Not sure when the replace function was introduced into mysql, but I
think it might do...
REPLACE() exists in 3.23.
Use replace in your order by, replacing a-z with null chars, leaving
just your numeric digits, then order by
Easier said than done.
select * from test;
+-+---+
| uid | name |
+-+---+
| 1 | george099 |
| 2 | george100 |
| 3 | george101 |
| 4 | george001 |
| 5 | 123bill |
| 6 | 100bill |
| 13 | george|
| 14 | darren|
| 15 | joe |
| 16 | bill |
+-+---+
10 rows in set (0.00 sec)
mysql select uid,name from test order by replace(name,'[a-z]','');
REPLACE doesn't accept patterns in the search string. This REPLACE is
looking for a literal occurrence of the string '[a-z]' to be replaced
with ''.
+-+---+
| uid | name |
+-+---+
| 6 | 100bill |
| 5 | 123bill |
| 16 | bill |
| 14 | darren|
| 13 | george|
| 4 | george001 |
| 1 | george099 |
| 2 | george100 |
| 3 | george101 |
| 15 | joe |
+-+---+
Look again. Those are in alphabetical order, not numerical.
You might need to convert 'name' to uppercase to work with all your
part
numbers.
select uid,name from test order by replace(upper(name),'[A-Z]','');
REPLACE is case-sensitive, but this method just won't work.
mysql SELECT REPLACE('123abcd45','[a-z]','');
+-+
| REPLACE('123abcd45','[a-z]','') |
+-+
| 123abcd45 |
+-+
1 row in set (0.11 sec)
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I isolate the integer part of a varchar field and use it in an ORDER BY?
Nicolas Verhaeghe wrote: Thanks, but unfortunately the replace function does not want to work on a regexp in version 3.23... or 4.0, or 4.1, or ... I guess I'll have to create a displacement field and populate it from the admin tool. Well, that's the right way to go. You're seeing the problem with the current scheme. Right now, your displacement is hidden inside the model number, so it is difficult to look up the displacement. That is, the model number contains the answers to more than one question. That's usually a bad idea. It probably ought to be broken into separate columns. If you make a displacement column, you ought to be able to populate it using sql. See below. Thanks for your help. I will upgrade this server as soon as I can. Upgrading is a good idea, but it won't help here. You can do this in sql, but it's a bit ugly. Here are the keys: * MySQL will pull out the number if it's at the beginning of the string. * You can change the beginning of the string with SUBSTRING(). * You can use CASE to handle the different possibilities. Putting them together gives you something like this: SELECT model FROM motorcycles ORDER BY CASE WHEN model 0 THEN model + 0 WHEN SUBSTRING(model, 2) 0 THEN SUBSTRING(model, 2) + 0 WHEN SUBSTRING(model, 3) 0 THEN SUBSTRING(model, 3) + 0 WHEN SUBSTRING(model, 4) 0 THEN SUBSTRING(model, 4) + 0 END; ++ | model | ++ | YZ85 | | YZ125 | | 125SX | | 250EXC | | WRF450 | | YZF450 | ++ 6 rows in set (0.00 sec) The first case handles the strings which start with a number. The second case handles the strings which starts with 1 letter before the number. The third case handles the string which start with 2 letters before the number. And so on. If you can have more than 3 letters before the number, you'll have to add the corresponding conditions. To just add and populate the displacement column, you could ALTER TABLE motorcycles ADD displacement INT, ADD INDEX disp_idx (displacement); UPDATE motorcycles SET displacement = CASE WHEN model 0 THEN model + 0 WHEN SUBSTRING(model, 2) 0 THEN SUBSTRING(model, 2) WHEN SUBSTRING(model, 3) 0 THEN SUBSTRING(model, 3) WHEN SUBSTRING(model, 4) 0 THEN SUBSTRING(model, 4) END; Then your query becomes simply SELECT model FROM motorcycles ORDER BY displacement; Better yet, the index on displacement can be used to speed up the ordering. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I isolate the integer part of a varchar field and use it in an ORDER BY?
Nicolas Verhaeghe wrote: Because I am currently stuck with 3.23 I have just decided to create a displacement field to isolate the number. That's the right way to go, regardless of version. Besides, some of these bikes escape from the rules, for instance instead of 600 for 600cc, you only have a mere 6, 1 stands for 1000. It's bad enough having to pull the displacement out of the model number, but when sometimes the number isn't really the displacement, what do you do? Well, if you could extend the rules to cover the exceptions, you could extend the CASE statement in my previous message to work, but this is really all the more reason to put displacement in its own column. So all in all the displacement fields will work fine. I think it's the only good solution. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I isolate the integer part of a varchar field and use it in an ORDER BY?
You're very helpful, thanks. Problem already taken care of but thanks for the lesson. -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 10:55 PM To: Nicolas Verhaeghe Cc: mysql@lists.mysql.com Subject: Re: How can I isolate the integer part of a varchar field and use it in an ORDER BY? Nicolas Verhaeghe wrote: Thanks, but unfortunately the replace function does not want to work on a regexp in version 3.23... or 4.0, or 4.1, or ... I guess I'll have to create a displacement field and populate it from the admin tool. Well, that's the right way to go. You're seeing the problem with the current scheme. Right now, your displacement is hidden inside the model number, so it is difficult to look up the displacement. That is, the model number contains the answers to more than one question. That's usually a bad idea. It probably ought to be broken into separate columns. If you make a displacement column, you ought to be able to populate it using sql. See below. Thanks for your help. I will upgrade this server as soon as I can. Upgrading is a good idea, but it won't help here. You can do this in sql, but it's a bit ugly. Here are the keys: * MySQL will pull out the number if it's at the beginning of the string. * You can change the beginning of the string with SUBSTRING(). * You can use CASE to handle the different possibilities. Putting them together gives you something like this: SELECT model FROM motorcycles ORDER BY CASE WHEN model 0 THEN model + 0 WHEN SUBSTRING(model, 2) 0 THEN SUBSTRING(model, 2) + 0 WHEN SUBSTRING(model, 3) 0 THEN SUBSTRING(model, 3) + 0 WHEN SUBSTRING(model, 4) 0 THEN SUBSTRING(model, 4) + 0 END; ++ | model | ++ | YZ85 | | YZ125 | | 125SX | | 250EXC | | WRF450 | | YZF450 | ++ 6 rows in set (0.00 sec) The first case handles the strings which start with a number. The second case handles the strings which starts with 1 letter before the number. The third case handles the string which start with 2 letters before the number. And so on. If you can have more than 3 letters before the number, you'll have to add the corresponding conditions. To just add and populate the displacement column, you could ALTER TABLE motorcycles ADD displacement INT, ADD INDEX disp_idx (displacement); UPDATE motorcycles SET displacement = CASE WHEN model 0 THEN model + 0 WHEN SUBSTRING(model, 2) 0 THEN SUBSTRING(model, 2) WHEN SUBSTRING(model, 3) 0 THEN SUBSTRING(model, 3) WHEN SUBSTRING(model, 4) 0 THEN SUBSTRING(model, 4) END; Then your query becomes simply SELECT model FROM motorcycles ORDER BY displacement; Better yet, the index on displacement can be used to speed up the ordering. Michael -- 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]
