Re: does DISTINCT kill ORDER BY?
Markus Hoenicka wrote: Hi, is the following behaviour intended? Are my queries wrong? The output shows only the Extra field as the other fields are identical in all cases. EXPLAIN SELECT t_refdb.refdb_id FROM t_refdb WHERE t_refdb.refdb_id0 ORDER BY t_refdb.refdb_id; = Using where; Using index (results are sorted) EXPLAIN SELECT DISTINCT t_refdb.refdb_id FROM t_refdb WHERE t_refdb.refdb_id0 ORDER BY t_refdb.refdb_id; = Using where; Using index (results are sorted) EXPLAIN SELECT t_refdb.refdb_id FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id0 ORDER BY t_refdb.refdb_id; = Using where; Using filesort (results are sorted) EXPLAIN SELECT DISTINCT t_refdb.refdb_id FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id0 ORDER BY t_refdb.refdb_id; = Using where (results are not sorted) That is, if MySQL can't use an index to sort the result, DISTINCT queries won't be sorted at all. That doesn't make sense. How many rows fit that criteria? ie: select count(distinct t_refdb.refdb_id) FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id0; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: does DISTINCT kill ORDER BY?
Chris [EMAIL PROTECTED] was heard to say: That is, if MySQL can't use an index to sort the result, DISTINCT queries won't be sorted at all. That doesn't make sense. How many rows fit that criteria? SELECT COUNT(DISTINCT t_refdb.refdb_id) FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id0; = 784 SELECT COUNT(DISTINCT t_refdb.refdb_id) FROM t_refdb WHERE t_refdb.refdb_id0; = 784 In this case refdb_type does not further restrict the result set. However, I've tried queries where a modified clause further restricts the results selected by refdb_id with the same effect regarding the sorting. BTW the above results were obtained with mysql.exe Ver 14.7 Distrib 4.1.10, for Win95/Win98 (i32) so the problem is neither specific to the FreeBSD port nor to a particular 4.1.x version. regards, Markus -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with mhoenicka) http://www.mhoenicka.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL on GHS Integrity..
Hi All, Anybody know how to install Mysql on GHS Integrity RTOS. Where I will get the mysql package for the same. Or can I use the same as for Linux. Regards, Ravi K 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
Re: does DISTINCT kill ORDER BY?
Just for the archives: looks like I bumped into bug #21456: http://bugs.mysql.com/bug.php?id=21456 This bug has been fixed in 4.1.22 and 5.0.25, so I'll just have to upgrade. Thanks anyway Markus Markus Hoenicka [EMAIL PROTECTED] was heard to say: Chris [EMAIL PROTECTED] was heard to say: That is, if MySQL can't use an index to sort the result, DISTINCT queries won't be sorted at all. That doesn't make sense. How many rows fit that criteria? SELECT COUNT(DISTINCT t_refdb.refdb_id) FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id0; = 784 SELECT COUNT(DISTINCT t_refdb.refdb_id) FROM t_refdb WHERE t_refdb.refdb_id0; = 784 In this case refdb_type does not further restrict the result set. However, I've tried queries where a modified clause further restricts the results selected by refdb_id with the same effect regarding the sorting. BTW the above results were obtained with mysql.exe Ver 14.7 Distrib 4.1.10, for Win95/Win98 (i32) so the problem is neither specific to the FreeBSD port nor to a particular 4.1.x version. regards, Markus -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with mhoenicka) http://www.mhoenicka.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with mhoenicka) http://www.mhoenicka.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ecommerce cart
Hi, I am starting a site that enables people to purchase information using credits bought on the site. e.g. you buy 10 credits for £10 and they sit in your online account on the site for you to spend whenever you like. Does anyone know of a script or software already in existance that we can buy to do this? Thanks for any help you can give. -- View this message in context: http://www.nabble.com/Ecommerce-cart-tf2226398.html#a6169649 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems Updating Database
I have a registration script that inserts data into the database, and then emails you an activation code to activate your account. The problem is that when I click on the link in the email, the account does not get updated. The portion of the script that has a problem is this: $query = UPDATE users SET active=NULL WHERE user_id=$x AND active=' . escape_data($y) . ' LIMIT 1; $result = mysql_query ($query) or trigger_error(Query: $query\nbr /MySQL Error: . mysql_error()); I do not believe this is a PHP problem because the error states that it is most likely a MySQL error. If anyone can see the problem, can you please let me know what it is. I have been looking at this for about three days now, and I can't figure it out. The registration page does update the DB (MySQL 3.24.49), but the activation page does not. Thank you, Nick
Re: Problems Updating Database
Which error do you get in activation page? Nicholas Vettese [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] I have a registration script that inserts data into the database, and then emails you an activation code to activate your account. The problem is that when I click on the link in the email, the account does not get updated. The portion of the script that has a problem is this: $query = UPDATE users SET active=NULL WHERE user_id=$x AND active=' . escape_data($y) . ' LIMIT 1; $result = mysql_query ($query) or trigger_error(Query: $query\nbr /MySQL Error: . mysql_error()); I do not believe this is a PHP problem because the error states that it is most likely a MySQL error. If anyone can see the problem, can you please let me know what it is. I have been looking at this for about three days now, and I can't figure it out. The registration page does update the DB (MySQL 3.24.49), but the activation page does not. Thank you, Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems Updating Database
I can see something that can be the problem, in mysql 3 you don´t have trigger, then you cannot use trigger_error function, try to use mysql_error instead. Nicholas Vettese [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] I have a registration script that inserts data into the database, and then emails you an activation code to activate your account. The problem is that when I click on the link in the email, the account does not get updated. The portion of the script that has a problem is this: $query = UPDATE users SET active=NULL WHERE user_id=$x AND active=' . escape_data($y) . ' LIMIT 1; $result = mysql_query ($query) or trigger_error(Query: $query\nbr /MySQL Error: . mysql_error()); I do not believe this is a PHP problem because the error states that it is most likely a MySQL error. If anyone can see the problem, can you please let me know what it is. I have been looking at this for about three days now, and I can't figure it out. The registration page does update the DB (MySQL 3.24.49), but the activation page does not. Thank you, Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL order by BUG?
I get the following query: select rt.team_id,count(p.race_id) as cnt,sum(p.points) as sum_score,sum(p.points) / count(p.race_id) as avg_score from some tables, joins and where-criterias group by rt.team_id order by avg_score desc; which gives: +-+--+---+---+ | team_id | cnt | sum_score | avg_score | +-+--+---+---+ | 163 |3 | 4.5 | 1.500 | | 312 |6 | 9.0 | 1.500 | | 223 |2 | 3.0 | 1.500 | | 167 |1 | 1.5 | 1.500 | But if I change to order by from 'avg_score desc' to 'avg_score,sum_score desc' I get: +-+--+---+---+ | team_id | cnt | sum_score | avg_score | +-+--+---+---+ | 262 |9 | 9.0 | 1.000 | | 161 |7 | 7.0 | 1.000 | | 317 |2 | 2.0 | 1.000 | | 97 |1 | 1.0 | 1.000 | | 143 |1 | 1.0 | 1.000 | The order has changed from desc to asc! Looks like a bug for me or am I blind? ;-) select version(); ++ | version() | ++ | 4.1.8-standard | ++ -- 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: Problems Updating Database
At 08:22 AM 9/6/2006, Nicholas Vettese wrote: I have a registration script that inserts data into the database, and then emails you an activation code to activate your account. The problem is that when I click on the link in the email, the account does not get updated. The portion of the script that has a problem is this: $query = UPDATE users SET active=NULL WHERE user_id=$x AND active=' . escape_data($y) . ' LIMIT 1; $result = mysql_query ($query) or trigger_error(Query: $query\nbr /MySQL Error: . mysql_error()); I do not believe this is a PHP problem because the error states that it is most likely a MySQL error. If anyone can see the problem, can you please let me know what it is. I have been looking at this for about three days now, and I can't figure it out. The registration page does update the DB (MySQL 3.24.49), but the activation page does not. Thank you, Nick Echo $query; test for existence of $x and $y. Are you sure you need trigger_error()? Why not just use the built-in error handling? In any case, what error message do you get? HTH - Miles -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.7/438 - Release Date: 9/5/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL order by BUG?
Try to use 'avg_score desc,sum_score desc' Jørn Dahl-Stamnes [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] I get the following query: select rt.team_id,count(p.race_id) as cnt,sum(p.points) as sum_score,sum(p.points) / count(p.race_id) as avg_score from some tables, joins and where-criterias group by rt.team_id order by avg_score desc; which gives: +-+--+---+---+ | team_id | cnt | sum_score | avg_score | +-+--+---+---+ | 163 |3 | 4.5 | 1.500 | | 312 |6 | 9.0 | 1.500 | | 223 |2 | 3.0 | 1.500 | | 167 |1 | 1.5 | 1.500 | But if I change to order by from 'avg_score desc' to 'avg_score,sum_score desc' I get: +-+--+---+---+ | team_id | cnt | sum_score | avg_score | +-+--+---+---+ | 262 |9 | 9.0 | 1.000 | | 161 |7 | 7.0 | 1.000 | | 317 |2 | 2.0 | 1.000 | | 97 |1 | 1.0 | 1.000 | | 143 |1 | 1.0 | 1.000 | The order has changed from desc to asc! Looks like a bug for me or am I blind? ;-) select version(); ++ | version() | ++ | 4.1.8-standard | ++ -- 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: SQL order by BUG?
2006/9/6, Jørn Dahl-Stamnes [EMAIL PROTECTED]: I get the following query: select rt.team_id,count(p.race_id) as cnt,sum(p.points) as sum_score,sum(p.points) / count(p.race_id) as avg_score from some tables, joins and where-criterias group by rt.team_id order by avg_score desc; which gives: +-+--+---+---+ | team_id | cnt | sum_score | avg_score | +-+--+---+---+ | 163 |3 | 4.5 | 1.500 | | 312 |6 | 9.0 | 1.500 | | 223 |2 | 3.0 | 1.500 | | 167 |1 | 1.5 | 1.500 | But if I change to order by from 'avg_score desc' to 'avg_score,sum_score desc' I get: since the default ordering is ASC and that the comma operator has precedence over DESC, your query is interpreted as : select rt.team_id,count(p.race_id) as cnt,sum(p.points) as sum_score,sum(p.points) / count(p.race_id) as avg_score from some tables, joins and where-criterias group by rt.team_id order by avg_score ASC,sum_score DESC; +-+--+---+---+ | team_id | cnt | sum_score | avg_score | +-+--+---+---+ | 262 |9 | 9.0 | 1.000 | | 161 |7 | 7.0 | 1.000 | | 317 |2 | 2.0 | 1.000 | | 97 |1 | 1.0 | 1.000 | | 143 |1 | 1.0 | 1.000 | The order has changed from desc to asc! Looks like a bug for me or am I blind? ;-) select version(); ++ | version() | ++ | 4.1.8-standard | ++ -- 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] -- http://www.myspace.com/sakuradrop : forget the rest http://www.w-fenec.org/ Webzine rock/metal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL order by BUG?
On Wednesday 06 September 2006 15:20, you wrote: the direction (desc/asc) order qualifier is per-sortfield and defaults to asc, so to get what you're after you'd want: order by avg_score desc, sum_score desc or order by avg_score desc, sum_score if you're after 'asc' on sum_score. - Rick Hmmm.. Seems like I have missed something important here. Thanks. -- 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: IN ANY subqueries
2006/9/5, Ben Lachman [EMAIL PROTECTED]:
So possibly a better solution would be to create a temporary table
and then do a subquery on that table?
yes. Or do a :
SELECT id FROM t1 WHERE name LIKE '%s1%' OR name LIKE %'s2%' OR name
LIKE '%s3%'
but depending on your data, a table may be the way to go.
-Ben
On Sep 4, 2006, at 7:37 AM, Visolve DB Team wrote:
Hi
The ANY keyword, which must follow a comparison operator, means
return TRUE if the comparison is TRUE for ANY of the values in the
column that the subquery returns
In has 2 forms:
1. IN (subquery). [The word IN is an alias for = ANY (subquery)].
2. IN (list of values seperated by comma)
Hence the exact syntax to use is:
Select fields from table where fieldname = ANY ( select
fieldname from table);
Ref: http://dev.mysql.com/doc/refman/5.0/en/any-in-some-
subqueries.html
Thanks
ViSolve DB Team
- Original Message -
From: Ben Lachman [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Sunday, September 03, 2006 10:33 AM
Subject: IN ANY subqueries
I have a string comparison that I would like to do against a short
list of constant strings.
at the moment I am using the syntax
SELECT id FROM t1 WHERE name IN('s1', 's2', 's3', ...);
However, this limits me to exact matches and I'd like to move to a
caparison expersion that lets me match names that contain any of the
list. The MySQL docs state that 'IN()' is an alias to '= ANY()'
however when I substitute' = ANY' for IN I get a parse error. What
I'd like to do is write something like (although I figure there may
be a better way to do the comparison that I am not thinking of):
SELECT id FROM t1 WHERE name LIKE ANY('%s1%', '%s2%', '%s3%', ...);
Does anyone know a way to do this?
Thanks,
-Ben
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
http://www.myspace.com/sakuradrop : forget the rest
http://www.w-fenec.org/ Webzine rock/metal
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL subqueries and JOIN conditions
2006/9/4, [EMAIL PROTECTED] [EMAIL PROTECTED]: Hello all, I need to perform an SQL statement over 5 different tables, with complex request. To do so, i'm using subqueries. SELECT ... FROM (SELECT .WHERE) as T1 LEFT JOIN (SELECT .WHERE) as T2 WHERE . The problem is that only JOIN seems working with subqueries (LEFT JOIN raise an error) What's the error you're getting ? Here is an example of results T1 and T2 (T1 and T2, as shown in the request before are result of subqueries) : Have you try to use a view instead of subselect ? -- http://www.myspace.com/sakuradrop : credit runs faster http://www.w-fenec.org/ Rock Webzine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT counting chars with LEFT()
Hi, I want to get all records from Tab1 and the first 20 words for the fieldA SELECT *, LEFT(fieldA, 20) AS fieldA FROM Tab1 But this query does not work: why? thanks! -- http://www.spacemarc.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems Updating Database
$x and $y work perfectly, and I believe the problem is the trigger function. Thanks to everyone who helped, and as soon as I get a chance to update and fix, I will try it. Thanks, Nick -Original Message- From: Miles Thompson [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 06, 2006 9:15 AM To: mysql@lists.mysql.com Subject: Re: Problems Updating Database At 08:22 AM 9/6/2006, Nicholas Vettese wrote: I have a registration script that inserts data into the database, and then emails you an activation code to activate your account. The problem is that when I click on the link in the email, the account does not get updated. The portion of the script that has a problem is this: $query = UPDATE users SET active=NULL WHERE user_id=$x AND active=' . escape_data($y) . ' LIMIT 1; $result = mysql_query ($query) or trigger_error(Query: $query\nbr /MySQL Error: . mysql_error()); I do not believe this is a PHP problem because the error states that it is most likely a MySQL error. If anyone can see the problem, can you please let me know what it is. I have been looking at this for about three days now, and I can't figure it out. The registration page does update the DB (MySQL 3.24.49), but the activation page does not. Thank you, Nick Echo $query; test for existence of $x and $y. Are you sure you need trigger_error()? Why not just use the built-in error handling? In any case, what error message do you get? HTH - Miles -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.7/438 - Release Date: 9/5/2006 -- 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: Adding and Removing tables from MERGE tables dynamically
I've got a similar setup, total records across about 8 tables hoovers around
200 million.
To change a merge table just issue an alter table with a new union.
ALTER TABLE mergetable UNION=(table1, table2, table3,...);
- Original Message -
From: Jacob, Raymond A Jr [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, September 05, 2006 5:29 PM
Subject: Adding and Removing tables from MERGE tables dynamically
Problem: I use two applications one called snort, the client that
inserts data into eleven(11) tables.
The other application BASE joins the tables into one table. When the
tables become 4GB in size,
deleting records for one month becomes unbearably slow(20-30days). The
search(Select ) is slow too but that
is a problem with the BASE application.
I thought that using MERGE tables would allow an administrator to create
a monthly table, using the original table names, composed of daily or
weekly
tables, by appending the date of creation to the table i.e.
data_table1_-MM-DD and join_table_-MM-DD. From the
documentation:
creating the table with INSERT_METHOD = FIRST results in INSERTs being
done to first table in the MERGE UNION statement.
I will assume that the first table is the latest table.
So one of the first tables should look like:
CREATE TABLE original_table
{
...
} TYPE = MERGE UNION = (data_table1_2006-09-12
,data_table1_2006-09-05)
Using cron and depending on the interval chosen daily, weekly,
bi-weekly, monthly, quarterly, or yearly at the start
of a new interval, a new table would be created with current date in
the -MM-DD format.
For example: if the start of new interval begins a week from today on
2006-09-12. At 12:00am on
2006-09-12, a script would create new tables that would look like:
CREATE TABLE data_table1_2006-09-12
{
...
}
One the MERGE TABLES should look like:
CREATE TABLE original_table
{
...
} TYPE = MERGE UNION = (data_table1_2006-09-12
,data_table1_2006-09-05)
On every Tuesday(in this case) from now on, new tables are created
ending with date in the format -MM-DD
and merged into the original table.
So that by 2006-09-30, one of the MERGE tables should look something
like
...
} TYPE = MERGE UNION = (data_table1_2006-09-26, data_table1_2006-09-19,
data_table1_2006-09-12, data_table1_2006-09-05)
On 2006-10-05 at 00:00hrs the newest table data_table1_2006-10-05
should be created and merged into the original_table. The oldest table
in this case data_table1_2006-09-05 should be removed from one of the
MERGE tables in this case original_table. The resulting merge table
should look something like
...
} TYPE = MERGE UNION = (data_table1_2006-10-05,data_table1_2006-09-26,
data_table1_2006-09-19, data_table1_2006-09-12)
Question: How does one add data_table1_2006-09-12 to original_table
dynamically?
Question: How does one remove data_table1_2006-09-05 from the
original_table dynamically?
Question: In other words, can tables be added and removed dynamically
to/from a MERGE TABLE?
Benefit: I hope is to archive individual tables. When I need to review
old data I will use a copy of the BASE application, then
Merge the tables that I am interested in, in order to search smaller
tables without changing the BASE application.
Question: Is this possible. Do these question make sense?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT counting chars with LEFT()
At 10:55 AM 9/6/2006, spacemarc wrote: Hi, I want to get all records from Tab1 and the first 20 words for the fieldA SELECT *, LEFT(fieldA, 20) AS fieldA FROM Tab1 But this query does not work: why? thanks! -- http://www.spacemarc.it First of all - please reply to the list .. I thought you wanted all fields, that's the way your SELECT statement is constructed. If just the first 20 char from fieldA, then ... SELECT LEFT(fieldA, 20) AS fieldA FROM Tab1 should do what you want. Again, check the syntax for LEFT() in the MySQL docs. Miles -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.7/438 - Release Date: 9/5/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT counting chars with LEFT()
2006/9/6, Miles Thompson [EMAIL PROTECTED]:
First of all - please reply to the list ..
I thought you wanted all fields, that's the way your SELECT statement is
constructed. If just the first 20 char from fieldA, then ...
SELECT LEFT(fieldA, 20) AS fieldA FROM Tab1
should do what you want. Again, check the syntax for LEFT() in the MySQL docs.
the syntax is valid:
from http://dev.mysql.com/doc/refman/5.0/en/string-functions.html:
LEFT(str,len) returns the leftmost len characters from the string
str, or NULL if any argument is NULL.
mysql SELECT LEFT('foobarbar', 5);
- 'fooba'
I have 30 fields: instead writing all 30 fields, I would want to
select them all with * and only for one of they, fieldA, to obtain the
first 20 chars: it's possible?
--
http://www.spacemarc.it
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT counting chars with LEFT()
You know, this works just fine for me -- when you say it is NOT working,
what do you mean exactly? Are you getting an error? Or results that you
don't expect?
Bill Tantzen
University of Minnesota Libraries
[EMAIL PROTECTED]
612-626-9949 (office) 612-250-6125 (cell)
I guess the man's a genius, but what
a dirty mind he has, hasn't he? -- Nora Joyce
-Original Message-
From: spacemarc [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 06, 2006 9:46 AM
To: Miles Thompson
Cc: mysql@lists.mysql.com
Subject: Re: SELECT counting chars with LEFT()
2006/9/6, Miles Thompson [EMAIL PROTECTED]:
First of all - please reply to the list ..
I thought you wanted all fields, that's the way your SELECT
statement
is constructed. If just the first 20 char from fieldA, then ...
SELECT LEFT(fieldA, 20) AS fieldA FROM Tab1 should do what
you want. Again, check the syntax for LEFT() in the MySQL docs.
the syntax is valid:
from http://dev.mysql.com/doc/refman/5.0/en/string-functions.html:
LEFT(str,len) returns the leftmost len characters from the
string str, or NULL if any argument is NULL.
mysql SELECT LEFT('foobarbar', 5);
- 'fooba'
I have 30 fields: instead writing all 30 fields, I would want
to select them all with * and only for one of they, fieldA,
to obtain the first 20 chars: it's possible?
--
http://www.spacemarc.it
--
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: SELECT counting chars with LEFT()
Hello MySQL-User,
i need your help with the following query.
I want to get the next birthdays of my friends, but not only the one of
this year.
So when im in december ( or november ) i want to see the birthdays of
next year too
With my query i only see the birthdays of this year :
SELECT SQL_CACHE DISTINCT fname,lname, mem.mem_id AS p_id,
DATE_FORMAT(mem.birthday,'%d.%m') AS geburtstag,
DATEDIFF(DATE_FORMAT(mem.birthday,CONCAT(DATE_FORMAT(NOW(),'%Y'),'-%m-%d')),DATE_FORMAT(NOW(),'%Y-%m-%d'))
as tage
FROM members mem
INNER JOIN network n ON ( ( n.mid = 1 AND n.action = 1 AND
n.bid=mem.mem_id ) OR ( n.bid = 1 AND n.action = 1 AND n.mid=mem.mem_id) )
WHERE DATE_SUB(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 1 DAY) =
DATE_FORMAT(mem.birthday,CONCAT(DATE_FORMAT(NOW(),'%Y'),'-%m-%d'))
ORDER BY tage
Thx a lot !!!
Cheers
Marco
spacemarc schrieb:
2006/9/6, Miles Thompson [EMAIL PROTECTED]:
First of all - please reply to the list ..
I thought you wanted all fields, that's the way your SELECT statement is
constructed. If just the first 20 char from fieldA, then ...
SELECT LEFT(fieldA, 20) AS fieldA FROM Tab1
should do what you want. Again, check the syntax for LEFT() in the
MySQL docs.
the syntax is valid:
from http://dev.mysql.com/doc/refman/5.0/en/string-functions.html:
LEFT(str,len) returns the leftmost len characters from the string
str, or NULL if any argument is NULL.
mysql SELECT LEFT('foobarbar', 5);
- 'fooba'
I have 30 fields: instead writing all 30 fields, I would want to
select them all with * and only for one of they, fieldA, to obtain the
first 20 chars: it's possible?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting next Birthdays
Hello MySQL-User,
i need your help with the following query.
I want to get the next birthdays of my friends, but not only the one of
this year.
So when im in december ( or november ) i want to see the birthdays of
next year too
With my query i only see the birthdays of this year :
SELECT SQL_CACHE DISTINCT fname,lname, mem.mem_id AS p_id,
DATE_FORMAT(mem.birthday,'%d.%m') AS geburtstag,
DATEDIFF(DATE_FORMAT(mem.birthday,CONCAT(DATE_FORMAT(NOW(),'%Y'),'-%m-%d')),DATE_FORMAT(NOW(),'%Y-%m-%d'))
as tage
FROM members mem
INNER JOIN network n ON ( ( n.mid = 1 AND n.action = 1 AND
n.bid=mem.mem_id ) OR ( n.bid = 1 AND n.action = 1 AND n.mid=mem.mem_id) )
WHERE DATE_SUB(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 1 DAY) =
DATE_FORMAT(mem.birthday,CONCAT(DATE_FORMAT(NOW(),'%Y'),'-%m-%d'))
ORDER BY tage
Thx a lot !!!
Cheers
Marco
spacemarc schrieb:
2006/9/6, Miles Thompson [EMAIL PROTECTED]:
First of all - please reply to the list ..
I thought you wanted all fields, that's the way your SELECT statement is
constructed. If just the first 20 char from fieldA, then ...
SELECT LEFT(fieldA, 20) AS fieldA FROM Tab1
should do what you want. Again, check the syntax for LEFT() in the
MySQL docs.
the syntax is valid:
from http://dev.mysql.com/doc/refman/5.0/en/string-functions.html:
LEFT(str,len) returns the leftmost len characters from the string
str, or NULL if any argument is NULL.
mysql SELECT LEFT('foobarbar', 5);
- 'fooba'
I have 30 fields: instead writing all 30 fields, I would want to
select them all with * and only for one of they, fieldA, to obtain the
first 20 chars: it's possible?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT counting chars with LEFT()
2006/9/6, Bill Tantzen [EMAIL PROTECTED]: You are actually selecting fieldA twice -- the first time (with '*') will return the entire column. The second time with only the first 20 chars. If you only want to see the first 20 chars, you will have to explicitly name every column in your select list -- you can't use '*'! ok, I wanted to avoid to write all the fields using * I will write every single field. thanks -- http://www.spacemarc.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
K12 Academics
Hello, My name is Chris Glavin. I emailed you because you are an educator. I run a website which is all about Education Disabilities. It is geared towards parents, students, teachers, district officials professionals. I ask that you take a few minutes out of your busy schedule to visit my site. I'm not trying to sell you anything. I simply want to show you a site I have made devoted to educators professionals. I will only email you this once. Thank you for your time. Below is a brief description of K12 Academics Visit K12 Academics. A National based Education Disability Resource Center with a community based approach. We cover every county, city or town in the U.S. If you are looking for a website or information on a school, organization, museum, library, camp or support in your area you can find it here. The site also serves as a great resource for Teachers, Parents, Students, Professionals District Officials in the K-12 Education system with tons of information dealing with education and disabilities. http://www.k12academics.com Cheers! Christopher Glavin K12academics.com This is a one time email. Please pass along. I will respond only to your reply. I strictly adhere to United States Federal Laws of Anti-spamming - CAN-SPAM Act of 2003. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem with InnoDB
Hi Dan, Thanks for yur response. Does it makes sense to create an index on a primary key ..as that is my smallest field ? Regards Prasad -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Monday, September 04, 2006 9:53 AM To: Prasad Ramisetti (WT01 - Broadband Networks) Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: problem with InnoDB In the last episode (Sep 04), [EMAIL PROTECTED] said: Actually there is some requirement, where I need to know the number of rows that I will get for my queries before actually executing the query. Could you please suggest some way for this. Your best bet is to create an index on the smallest column you can find (maybe even create a char(1) and leave it empty), and SELECT COUNT(*) FROM innotable USE INDEX (smallcolumn). That way mysql only has to scan a small secondary index instead of the main table index. -- Dan Nelson [EMAIL PROTECTED] 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]
Re: problem with InnoDB
[EMAIL PROTECTED] wrote: Hi Dan, Thanks for yur response. Does it makes sense to create an index on a primary key ..as that is my smallest field ? A primary key already has an index. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with InnoDB
Hi Prasad A primary key automatically creates an index and not-null and unique constraints, too. So you don't need to explicitly create an index on a primary key column. If your queries are going to have WHERE clauses (as they most likely are) I'm not sure how the small-index suggestion would make the query any faster - the WHERE clause would preclude the use of that index in computing the rows - but I'm probably missing something here. When you say that you need to know the number of rows returned before executing the query, do you mean before you start getting rows back or before you actually execute the query? I don't think it's possible to know how many rows the query will return without actually executing it, but you might well want to know how many rows you get before you start processing rows. Have you looked at the SQL_CALC_FOUND_ROWS option on SELECT, and the accompanying FOUND_ROWS() function? http://dev.mysql.com/doc/refman/ 5.0/en/information-functions.html It will tell you the total number of rows which would have been found if you hadn't used a LIMIT clause. I think it is a connection- specific function; if you created a second statement handle and did a SELECT FOUND_ROWS() on the same connection, perhaps that would give what you want. Douglas Sims [EMAIL PROTECTED] On Sep 6, 2006, at 11:29 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi Dan, Thanks for yur response. Does it makes sense to create an index on a primary key ..as that is my smallest field ? Regards Prasad -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Monday, September 04, 2006 9:53 AM To: Prasad Ramisetti (WT01 - Broadband Networks) Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: problem with InnoDB In the last episode (Sep 04), [EMAIL PROTECTED] said: Actually there is some requirement, where I need to know the number of rows that I will get for my queries before actually executing the query. Could you please suggest some way for this. Your best bet is to create an index on the smallest column you can find (maybe even create a char(1) and leave it empty), and SELECT COUNT(*) FROM innotable USE INDEX (smallcolumn). That way mysql only has to scan a small secondary index instead of the main table index. -- Dan Nelson [EMAIL PROTECTED] 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with InnoDB
In the last episode (Sep 07), [EMAIL PROTECTED] said: Hi Dan, Thanks for yur response. Does it makes sense to create an index on a primary key ..as that is my smallest field ? It might, because in an InnoDB table, your primary index also holds your row data. So it's actually your largest index. A full scan of a secondary index on your primary key may very well run faster than a scan of the primary index itself, for the purposes of SELECT COUNT(*). Best way to find out is to try it :) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
