Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'

Mike Spreitzer wrote: So I am trying to use mysqldump --tab for the first time. I am running into the same problem everybody does on their first try --- Errcode: 13. I have set the permissions on the target directory to be completely liberal --- anybody can do anything with it --- and I still

Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'

' (Errcode: 13) when executing 'SELECT INTO OUTFILE' [EMAIL PROTECTED] /]# ls -ld /dump1 drwxrwxrwx 2 mysql mysql 4096 Feb 22 19:11 /dump1 [EMAIL PROTECTED] /]# ls -l /dump1 total 16 -rw-r--r-- 1 mysql mysql 29 Feb 22 18:38 foo.bar -rw-r--r-- 1 root root 742 Feb 22 19:11 red1_p2.sql [EMAIL

Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'

what happens when you delete the files that are already in there? Looks like you're dumping to a file owned by root. Jed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'

to file '/dump1/red1_p2.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' [EMAIL PROTECTED] /]# ls -l dump1 total 8 -rw-r--r-- 1 root root 742 Feb 22 20:46 red1_p2.sql [EMAIL PROTECTED] /]# ps axlw | grep mysqld 0 0 8494 1 25 0 3408 944 wait Spts/5 0:00 /bin/sh /usr

Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'

: mysqldump: Got error: 1: Can't create/write to file '/dump1/red1_p2.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' I'm reading thru http://dev.mysql.com/doc/refman/5.0/en/select.html and it says that it will not write to an already existing file (so you deleted the old files

Re: group a select * and a select COUNT from 2 different [... ] (solved thankyou !)

Hello, thankyou to everyone who has helped me out on this one as I did not think it was actuallay possible ! :) This is what worked best for me : SELECT a.username, a.first_name, a.last_name,COALESCE(COUNT(b.username), 0) AS count FROM user_list a LEFT JOIN login_table b ON a.username

group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?

the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b

Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?

Richard, Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table

Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?

(because of the group by ...). Thankyou Peter Brawley a écrit : Richard, Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name

Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?

Sorry it's me again, I made a mistake, it counts the number of logins correctly, but does not show members with 0 logins ! Any idea how to do this? Thanks :) Peter Brawley a écrit : Richard, Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT

Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?

Try SELECT a.username, a.first_name, a.last_name, SUM(case when b.username is null then 0 else 1 end) as count FROM user_list a LEFT OUTER JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; Donna Richard [EMAIL PROTECTED] 02/19/2008 05:29 PM

Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?

Actually, this works too: SELECT a.username, a.first_name, a.last_name, Count(b.username) as count FROM user_list a LEFT OUTER JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; __ Try SELECT a.username, a.first_name, a.last_name

Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?

Try this one: SELECT a.username, a.first_name, a.last_name,COALESCE(COUNT(b.username), 0) AS count FROM user_list a LEFT JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; The LEFT JOIN will ensure you still get a result row even

Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?

logged in or not? #2 is less clear still; does it mean the query is to show a count of 0 for no logins and 1 for any positive number of logins? If so, try... SELECT a.username, a.first_name, a.last_name,COUNT(DISTINCT b.username) AS count FROM user_list a LEFT JOIN login_table b ON a.username

A sql/select question.

SC12033216729280 T14 1203321672 M13 1203321803 SC12033216819810 T15 1203321681 M15 1203321809 SC12033216898223 T11 1203321689 M13 1203321816 How to select out data with below format? job_idsubmit_user_id

Re: A sql/select question.

SC12033216636547 T12 1203321663 M11 1203321796 SC12033216729280 T14 1203321672 M13 1203321803 SC12033216819810 T15 1203321681 M15 1203321809 SC12033216898223 T11 1203321689 M13 1203321816 How to select out

Re: A sql/select question.

Hi Rob, That is just what I need. I can't use 'join' very well yet. Thank you very much. -- King Kwok -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: select unique ? (solved thankyou :))

Price, Randall a écrit : Since both of these work, I was wondering which one would be faster. Here is an EXPLAIN on a similar test I did on one of my test tables. (NO index on field1, WinXP, MySQL 5.0.41-community-nt, SQLyog query window) SELECT COUNT(*) FROM tblClients (1660 row(s

Re: select unique ?

) SELECT COUNT(*) FROM tblClients (1660 row(s) returned) (0 ms taken) RESET QUERY CACHE SELECT DISTINCT field1 FROM tblClients (130 row(s) returned) (0 ms taken) EXPLAIN SELECT DISTINCT field1 FROM tblClients /* 1457 rows, Using temporary */ RESET QUERY CACHE

select with table name

Hi, I have a small issue that i can get my head around to solve: Is it possible to do a select from two tables using a union all (select * from t1 union all select * from t2), and have it display the table name in front of each row? What i need is, on the big resulting

Re: select with table name

At 12:04 AM + 2/17/08, Miguel Vaz wrote: Hi, I have a small issue that i can get my head around to solve: Is it possible to do a select from two tables using a union all (select * from t1 union all select * from t2), and have it display the table name in front of each

RE: select with table name

Can you just do SELECT t1.*, t1 as tablename FROM t1 UNION SELECT t2.*, t2 as tablename FROM t2 Roger -Original Message- From: Miguel Vaz [mailto:[EMAIL PROTECTED] Sent: 17 February 2008 00:04 To: mysql@lists.mysql.com Subject: select with table name Hi, I have

RE: select unique ?

Since both of these work, I was wondering which one would be faster. Here is an EXPLAIN on a similar test I did on one of my test tables. (NO index on field1, WinXP, MySQL 5.0.41-community-nt, SQLyog query window) SELECT COUNT(*) FROM tblClients (1660 row(s) returned) (0 ms taken

select unique ?

Hello, I don't know if it is possible to do this with mysql alone ... Here goes : I've got a database list which is like to following : Num | Name| Colour --- 1 | Harry | Red 2 | Tom | Blue 3

Re: select unique ?

SELECT DISTINCT Colour FROM tablename Peter On 14/02/2008, Richard [EMAIL PROTECTED] wrote: Hello, I don't know if it is possible to do this with mysql alone ... Here goes : I've got a database list which is like to following : Num | Name| Colour

Re: select unique ?

Try: SELECT DISTINCT Colour FROM table; Or, if you want to do it correctly: SELECT Colour FROM table GROUP BY color; Richard wrote: Hello, I don't know if it is possible to do this with mysql alone ... Here goes : I've got a database list which is like to following : Num|Name

very strange slow plain select

Hello: I just stumbled on this hard to explain problem. Below select * from BigTable  (BigTable has 5 million rows) takes forever to even print out the first row. as you could see from the sql, it is supposed to print out some rows right away regardless of server config in my.cnf.   What

Re: very strange slow plain select

In the last episode (Feb 07), [EMAIL PROTECTED] said: I just stumbled on this hard to explain problem. Below select * from BigTable  (BigTable has 5 million rows) takes forever to even print out the first row. as you could see from the sql, it is supposed to print out some rows right

Re: very strange slow plain select

this time so the cache would have been filled long before that. Kind of puzzling. In the last episode (Feb 07), [EMAIL PROTECTED] said: I just stumbled on this hard to explain problem. Below select * from BigTable  (BigTable has 5 million rows) takes forever to even print out the first row

Re: very strange slow plain select

enough to read in more than 2 GB data during this time so the cache would have been filled long before that. Kind of puzzling. Have you tried Select SQL_NO_CACHE col1,col2 from table ...? If you are returning more than 10,000 rows at a time, why not use the LIMIT Offset,Limit to pull in just

feature request: statement SELECT...(INSERT|UPDATE) :)

mysql_insert_id() and the discription of SELECT LAST_INSERT_ID() in new versions, but as far as I've understood it concerns only AUTOINCREMENT columns, and very often it isn't enough (some columns may be filled by mysql functions (for example data/time/mathematical functions etc) and very often

Re: feature request: statement SELECT...(INSERT|UPDATE) :)

On Jan 30, 2008 12:50 PM, Dmitry E. Oboukhov wrote: Is it possible to add to the syntax of the INSERT operator appoximately in such way: SELECT list INSERT [IGNORE] INTO ... - an added one. SELECT list UPDATE - an added one. PS: I understand that adding the changes into a language

Re: feature request: statement SELECT...(INSERT|UPDATE) :)

On Jan 30, 2008 12:50 PM, Dmitry E. Oboukhov wrote: Is it possible to add to the syntax of the INSERT operator appoximately in such way: SELECT list INSERT [IGNORE] INTO ... - an added one. SELECT list UPDATE - an added one. PS: I understand that adding the changes

Re: select from otherdb.table question?

, Alex K wrote: Hi Guys, What does the statement select * from otherdb.table do if I haven't explicitly connected to otherdb previously? I would assume it connects to otherdb and does the select on table but does it create a new connection each time? Is it as efficient as explicitly connecting

Re: select from otherdb.table question?

are working with. So to answer your question, no, a new connection is not established. Brent On Jan 19, 2008, at 10:19 AM, Alex K wrote: Hi Guys, What does the statement select * from otherdb.table do if I haven't explicitly connected to otherdb previously? I would assume it connects

select from otherdb.table question?

Hi Guys, What does the statement select * from otherdb.table do if I haven't explicitly connected to otherdb previously? I would assume it connects to otherdb and does the select on table but does it create a new connection each time? Is it as efficient as explicitly connecting to otherdb

Re: select

-01-01 11 2000-02-15 12 2000-03-20 13 2000-05-18 how do i construct my select statement so that i only get distinct year? so the above data will return something like this: sDate 2000 1999 1998 1997 did your tried: SELECT DISTINCT YEAR(`sDate`); -- Sebastian thanks, that's

Re: select

2000-05-18 how do i construct my select statement so that i only get distinct year? so the above data will return something like this: sDate 2000 1999 1998 1997 did your tried: SELECT DISTINCT YEAR(`sDate`); -- Sebastian -- MySQL General Mailing List For list archives: http

Re: Select statement help

to change the field names, as I am simply going to import again when the database needs updating. How can I select these fields from the table? - SELECT F(1), S(1) doesn't work, SELECT F(1) , S(1) doesn't work and neither does SELECT [F(1)], [S(1)] Quote the names with backticks: `F(1)`, `S

Select statement help

the database needs updating. How can I select these fields from the table? - SELECT F(1), S(1) doesn't work, SELECT F(1) , S(1) doesn't work and neither does SELECT [F(1)], [S(1)] Thanks, in anticipation! Rory -- View this message in context: http://www.nabble.com/Select-statement-help

select

i construct my select statement so that i only get distinct year? so the above data will return something like this: sDate 2000 1999 1998 1997 thanks, t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL

Select multiple rows with no reference table

Hey folks. I just joined the list. Please let me know if I am not posting to the correct list. I am trying to use a select statement to retreive multiple records without a reference table. Obviously retreiving a single record is simple enough: mysql SELECT 'value1' AS var1, 'value2' AS var2

Re: Select multiple rows with no reference table

Hi Mark, On Jan 15, 2008 12:51 PM, Mark Wexler [EMAIL PROTECTED] wrote: Hey folks. I just joined the list. Please let me know if I am not posting to the correct list. You're in the right list. I am trying to use a select statement to retreive multiple records without a reference table

RE: Select multiple rows with no reference table

Thank you -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Baron Schwartz Sent: Tuesday, January 15, 2008 1:54 PM To: Mark Wexler Cc: mysql@lists.mysql.com Subject: Re: Select multiple rows with no reference table Hi Mark, On Jan 15, 2008 12:51 PM

MySQL SELECT Statement with Date help request

to 2007-03-01 db3 : 2007-03-01 to 2007-04-01 db4 : 2007-04-01 to 2007-05-01 db5 : 2007-05-01 to 2007-06-01 db6 : 2007-06-01 to 2007-07-01 I require a select statement to query all the db's to identify records with the date range for eg. 2007-02-15 to 2007-05-12. Logically this tells me

Re: MySQL SELECT Statement with Date help request

-01 db3 : 2007-03-01 to 2007-04-01 db4 : 2007-04-01 to 2007-05-01 db5 : 2007-05-01 to 2007-06-01 db6 : 2007-06-01 to 2007-07-01 I require a select statement to query all the db's to identify records with the date range for eg. 2007-02-15 to 2007-05-12. Logically this tells me that the databases

Re: derived select can't find table

Hi, On Dec 26, 2007 11:02 PM, wang shuming [EMAIL PROTECTED] wrote: Hi, select * , ( select f2 from (select f1,f2 from t2 where t2.id=t1.id limit 1 union all ... order by f1 ) t2 limit 1 ) f2 from t1 Unknown column ' t1.id

derived select can't find table

Hi, select * , ( select f2 from (select f1,f2 from t2 where t2.id=t1.id limit 1 union all ... order by f1 ) t2 limit 1 ) f2 from t1 Unknown column ' t1.id' in 'where clause' Best regard! Shuming Wang

Applying LIMIT to SELECT count(*)

Hi, My task is to limit calculation of total number of items in the database that satisfy certain conditions. I join two tables using WHERE and there are millions of records as the result. When I do SELECT count(*) it takes really too long. The table has appropriate indexes and I

Re: Applying LIMIT to SELECT count(*)

tables using WHERE and there are millions of records as the result. When I do SELECT count(*) it takes really too long. The table has appropriate indexes and I experimented with replacing the conditions, etc., so I don't think there is a way to make it work any faster. In my case it would

Re: Applying LIMIT to SELECT count(*)

The problem is that there are certain conditions after WHERE different for each query and the results number can be very different. -- View this message in context: http://www.nabble.com/Applying-LIMIT-to-SELECT-count%28*%29-tp14453544p14459808.html Sent from the MySQL - General mailing list

Re: Applying LIMIT to SELECT count(*)

: http://www.nabble.com/Applying-LIMIT-to-SELECT-count%28*%29-tp14453544p14459808.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED

Can slow-query-log option only record select statement?

I want to know how to configurate slow-query-log to let it not record the update sql. I just want to know how the slow select statement ,not the update or insert. Anybody's reply is appreciated,thanks. -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn

Select mit Subselect Problem

bei 5 Mio Datensätze in den Abverkäufen PHP schlapp. DArum meine Frage kann ich effizienter Mit MySQL machen? Bisher SELECT ArtikelNr,Kassenbon,Haendler,Datum FROM sales s WHERE ArtikelNr = '10099' SCHLEIFE mit Subselect SELECT * FROM sales WHERE ArtikelNr='$ArtikelNr' AND Kassenbon

Re: Select mit Subselect Problem

At 7:45a -0500 on 17 Dec 2007, Marcus Füg wrote: Tabelle Sales (s) mit jede Menge Abverkaufsdaten. ArtikelNr,Kassenbon,HändlerID,Datum Jetzt sollen alle zu einem gekauften Artikel ebenfalls augelistet werden, d.h. was wurde mit dem Artikel zusätzliche gekauft Bisher habe ich das mit PHP

help with select

| betty | smith 7 | bob | Gomez 10 | sun | mcnab i'm trying to select from idea table such that when iid = 1, i should get betty smith for completed_by column and sun mcnab for submitted_by column. 1st trial: select iid,completed_by,submitted_by from idea where iid=1 i got: 1 | 4 | 10

Re: help with select

You might try this: SELECT I.iid, CONCAT(ECOMP.last, ', ', ECOMP.first) AS 'Completed By', CONCAT(ESUB.last, ', ', ESUB.first) AS 'Submitted By', FROM idea I JOIN employee ECOMP ON I.completed_by = ECOMP.eid JOIN employee ESUB ON I.submitted_by = ESUB.eid andy Hiep Nguyen wrote

Re: help with select

mysql select idea.iid, concat(employee.first, ,employee.last),(SELECT concat( employee.first, ,employee.last) FROM EMPLOYEE where employee.eid=idea.submitted_by) from ide a,employee where idea.iid=1 and idea.completed_by=employee.eid; M-- - Original Message - From: Hiep Nguyen [EMAIL

Re: SELECT Speed

At 05:57 PM 11/26/2007, you wrote: The second query might be faster due to caching. This can be verified by executing: RESET QUERY CACHE before executing the second query. This will clear the queries from the cache. Mike -- MySQL General Mailing List For list archives:

Re: SELECT Speed

On Nov 27, 2007 10:21 AM, mos [EMAIL PROTECTED] wrote: At 05:57 PM 11/26/2007, you wrote: The second query might be faster due to caching. This can be verified by executing: RESET QUERY CACHE before executing the second query. This will clear the queries from the cache. No need to blow

SELECT speed

Hi, I have a problem with SELECT speed. The first execution takes up to several minutes while the next (with the same statement) takes not more then several seconds. The statement example is: select nas.nasIpAddress, count(distinct(acct.user_id)), count(*), sum(acct.acctOutputOctets) from acct

SELECT Speed

Hi, I have a problem with SELECT speed. The first execution takes up to several minutes while the next (with the same statement) takes not more then several seconds. The statement example is: select nas.nasIpAddress, count(distinct(acct.user_id)), count(*), sum(acct.acctOutputOctets) from acct

Re: SELECT Speed

, 2007 10:03 AM Subject: SELECT Speed Hi, I have a problem with SELECT speed. The first execution takes up to several minutes while the next (with the same statement) takes not more then several seconds. The statement example is: select nas.nasIpAddress, count(distinct(acct.user_id)), count

Re: SELECT Speed

The second query might be faster due to caching. On 11/26/07, Alexander Bespalov [EMAIL PROTECTED] wrote: Hi, I have a problem with SELECT speed. The first execution takes up to several minutes while the next (with the same statement) takes not more then several seconds. The statement

Select rows containing identical values in two columns

I have a table that has a column with the id of the person that created the row. In another column in the same row there is a column with the id of the person that modified that row. Is there a way to write a SELECT statement that will return all the rows where the value in the creation

Re: Select rows containing identical values in two columns

I have a table that has a column with the id of the person that created the row. In another column in the same row there is a column with the id of the person that modified that row. Is there a way to write a SELECT statement that will return all the rows where the value in the creation

Re: Use select within delete

a select command. For instance, delete from table1 where id=(select id from table2 where dateoneweek) Is it possible in mysql 4.0.24?? Thanks in advance Dario Not until 4.1. What you can do instead is run the select into a temp table and then run the delete as a join with that temp

Re: Use select within delete

Hernan wrote: Hi all I need to delete some fields from a table but in the where clause I need to put a select command. For instance, delete from table1 where id=(select id from table2 where dateoneweek) Is it possible in mysql 4.0.24?? Thanks in advance Dario Not until

Use select within delete

Hi all I need to delete some fields from a table but in the where clause I need to put a select command. For instance, delete from table1 where id=(select id from table2 where dateoneweek) Is it possible in mysql 4.0.24?? Thanks in advance Dario -- MySQL General Mailing List For list archives

Re: Use select within delete

Dario Hernan wrote: Hi all I need to delete some fields from a table but in the where clause I need to put a select command. For instance, delete from table1 where id=(select id from table2 where dateoneweek) Is it possible in mysql 4.0.24?? Thanks in advance Dario Not until 4.1. What you

select sum order

hi there, this seems so easy, but i'm out of sql for a long time and need help i have: id,amount,state 1,2.00,il 2,2.00,oh 3,1.00,il 4,1.00,ks 5,3.00,ks 6,4.00,oh how do i construct a sql statement that results as following: il,3.0 oh,6.0 ks,4.0 sum (amount) all the same state. thank

Re: select sum order

the same state. Try this: select state, sum(amount) from tbl group by state; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: select sum order

that results as following: il,3.0 oh,6.0 ks,4.0 sum (amount) all the same state. Try this: select state, sum(amount) from tbl group by state; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Select question

I've got this statement to select the last two entries in my db: SELECT top 2 * FROM Content ORDER BY ContentID desc and it works fine because it selects the last two items entered into the db. However, I only want to be able to select item 2 rather than both 1 and 2. How do I do

Re: Select question

Matthew Stuart schrieb: I've got this statement to select the last two entries in my db: SELECT top 2 * FROM Content ORDER BY ContentID desc and it works fine because it selects the last two items entered into the db. However, I only want to be able to select item 2 rather than both 1

RE: Select question

: Thursday, October 25, 2007 6:55 AM To: MySQL email support Subject: Select question I've got this statement to select the last two entries in my db: SELECT top 2 * FROM Content ORDER BY ContentID desc and it works fine because it selects the last two items entered into the db. However, I

Why can't I still not use an ALIAS in the SELECT portion of an SQL statement?

When will I be able to do something seemingly so basic as this re-use of an alias? SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR) AS group_duration_date, UNIX_TIMESTAMP(group_duration_date) AS group_duration_date_timestamp FROM

Re: Why can't I still not use an ALIAS in the SELECT portion of an SQL statement?

When will I be able to do something seemingly so basic as this re-use of an alias? Do you know an implementation of SQL which allows this? PB Daevid Vincent wrote: When will I be able to do something seemingly so basic as this re-use of an alias? SELECT DATE_ADD('2007-10-23', INTERVAL

RE: Why can't I still not use an ALIAS in the SELECT portion of an SQL statement?

] Sent: Tuesday, October 23, 2007 5:39 PM To: Daevid Vincent Cc: mysql@lists.mysql.com Subject: Re: Why can't I still not use an ALIAS in the SELECT portion of an SQL statement? When will I be able to do something seemingly so basic as this re-use of an alias? Do you know

Re: Why can't I still not use an ALIAS in the SELECT portion of an SQL statement?

as a *terrible* reason. It seems stupid that I can't do that though. I can use the alias in the HAVING clause, and also in an ORDER BY clause. Yes, but those are HAVING and ORDER BY clauses. You recognise that they are distinct parts of a SELECT statement, so ... I'm not saying it's a trivial change

Re: Why can't I still not use an ALIAS in the SELECT portion of an SQL statement?

? This strikes me as a *terrible* reason. It seems stupid that I can't do that though. I can use the alias in the HAVING clause, and also in an ORDER BY clause. Yes, but those are HAVING and ORDER BY clauses. You recognise that they are distinct parts of a SELECT statement, so ... I'm

How to know the number of rows used in a SELECT MAX() query? FOUND_ROWS() not working.

Is there a way to know how many rows were used in a computation? I tried this 'trick' but I still get 1, when I know that there are 3 rows used... SELECT SQL_CALC_FOUND_ROWS MAX(DATE_ADD('2007-10-18 18:04:45', INTERVAL user_access_hours HOUR)), MAX(access_expire) FROM

Re: How to know the number of rows used in a SELECT MAX() query? FOUND_ROWS() not working.

Hi, Daevid Vincent wrote: Is there a way to know how many rows were used in a computation? I tried this 'trick' but I still get 1, when I know that there are 3 rows used... SELECT SQL_CALC_FOUND_ROWS MAX(DATE_ADD('2007-10-18 18:04:45', INTERVAL user_access_hours HOUR)), MAX(access_expire

Bug?: SELECT *, Field vs. SELECT Field, *

Hi, When i try: SELECT *, id FROM table all is fine. But if i want the * at the end (because the table is large) i try: SELECT id, * FROM table and got: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

Re: Deadlocks with High Concurrency SELECT FOR UPDATE

Hi William, William Newton wrote: Hello List, I have this table that has a single row in it: CREATE TABLE `quicktable` ( `x` int(11) NOT NULL auto_increment, `quick_id` int(11) NOT NULL default '0', PRIMARY KEY (`x`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 select

Deadlocks with High Concurrency SELECT FOR UPDATE

Hello List, I have this table that has a single row in it: CREATE TABLE `quicktable` ( `x` int(11) NOT NULL auto_increment, `quick_id` int(11) NOT NULL default '0', PRIMARY KEY (`x`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 select * from quicktable; +---+--+ | x

LEFT JOIN (SELECT ...) -- no joy

) unsigned | NO | | | | ++---+--+-+-++ 5 rows in set (0.01 sec) mysql SELECT order_id, SUM(quantity * unit_price) AS subtotal - FROM order_details GROUP BY order_id; +--+--+ | order_id | subtotal

Re: LEFT JOIN (SELECT ...) -- no joy

Andrew Carlson wrote: Is there a purchase order (10002) with no orders in the order_details table? No, PO 10002 is related to the order_id = 2 here: mysql SELECT order_id, SUM(quantity * unit_price) AS subtotal - FROM order_details GROUP BY order_id; +--+--+ | order_id

how to select second records in a group

I was a bit stumped on a good method to select the second record for each distinct group in a table. Say I have a table like the following: NAME DATE AMOUNT joe 2007-10-03 19:44:57 45 joe 2007-10-06 19:46:18 90 joe 2007-10-07 19:37:21 12 matt2007-10

Re: how to select second records in a group

Hi Steve, Steve Kiehl wrote: I was a bit stumped on a good method to select the second record for each distinct group in a table. Say I have a table like the following: NAME DATE AMOUNT joe 2007-10-03 19:44:57 45 joe 2007-10-06 19:46:18 90 joe 2007-10-07 19:37:21

SELECT ... LIKE and the Korean character set

Hello. I have a question about the behavior of SELECT ... LIKE and dealing with the Korean language. For those who don't know anything about the way the language's characters are formulated, I'll give you a quick crash course: Korean has its own alphabet, just like Japanese, Chinese and most

Re: Question related to INSERT statement into table1 and SELECT statement from table1 to populate a column field into table1

I have a table with a PRIMARY KEY on id field, whos evalue is populated usin auto_increment. CREATE TABLE `key` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `sid` smallint(4) unsigned NOT NULL DEFAULT '0', `email` varchar(128) NOT NULL DEFAULT '', PRIMARY KEY (`id`),

Re: Big SELECT: ordering results by where matches are found

result was lower with plain UNION than if I used UNION ALL. As far as I can tell from my relatively limited experience with all this, the first row refers to my outer 'wrapper' select from the derived table (the table in the first row is given as 'derived2' and the Extra column shows 'Using

Big SELECT: ordering results by where matches are found

I'm sure there must be an accepted technique for this, but it's something I haven't tried before, so if anyone can point me in the right direction I'd be grateful. I'm writing a search facility for a site where the data is stored in several tables - let's say 5 for this example - and I want

Re: Big SELECT: ordering results by where matches are found

Chris Sansom wrote: I'm sure there must be an accepted technique for this, but it's something I haven't tried before, so if anyone can point me in the right direction I'd be grateful. I'm writing a search facility for a site where the data is stored in several tables - let's say 5 for this

Re: Big SELECT: ordering results by where matches are found

At 11:01 -0400 10/9/07, Baron Schwartz wrote: I've built similar systems with a series of UNION queries. Each UNION has a column for relevance, which can be a sum of CASE statements, such as IF(name matches, 1, 0) + IF(text matches, 1, 0) AS relevance... The entire UNION can then be ordered by

Re: Big SELECT: ordering results by where matches are found

the trick. I read up on it on the MySQL docs site and I've ended up with this, which actually covers more tables and fields than in my original post: --- select distinct tb.speaker_id, tb.fore, tb.sur, tb.division from ( ( select 1 as relevance, speaker_id, fore, sur, division from

Re: Big SELECT: ordering results by where matches are found

UNION may have done the trick. I read up on it on the MySQL docs site and I've ended up with this, which actually covers more tables and fields than in my original post: --- select distinct tb.speaker_id, tb.fore, tb.sur, tb.division from ( ( select 1 as relevance, speaker_id, fore

A select for a game ranking page.

through all the records, so my plan was to do a SELECT count(*) and have WHERE top_score the players top score.. however when there are many scores the same I want to also do WHERE number_of_plays the players number of plays. Doing WHERE top_score 1000 AND number_of_plays 10 is no good as some

Re: A select for a game ranking page.

without looping through all the records, so my plan was to do a SELECT count(*) and have WHERE top_score the players top score.. however when there are many scores the same I want to also do WHERE number_of_plays the players number of plays. Doing WHERE top_score 1000 AND number_of_plays

<    2   3   4   5   6   7   8   9   10   11   >