Complicated Subquery help
Hiya Folks! I'm struggling with a complicated subquery issue, and my SQL-fu isn't strong enough to make a solution apparent. Hopefully one of you experts can give me a nudge in the right direction :) The query utilizes data from just one table, which looks like: idX integer idY tinyint unsigned, idZ tinyint, c1 smallint, c2 smallint, c3 smallint, ... [and so on] ... What I'd like to do is select a set of the channel data (the c* fields) based on rows where the standard deviation on a given channel is below a certain threshold for the rows "near" that one, based on the values of the id fields. Getting the standard deviation on a single field for a given row isn't too bad: select std(c2330) from radiances where idZ between 44 and 46 and idX between 12 and 14, and idX = 7; That query selects the standard deviation for channel 2330, in the 9 "footprints" around the current point I'm looking at. Selecting the channels I need to analyze based on that should just using that select inside an IF statement. The trick is, how to automate this to iterate over all the id* fields? idX is an auto_increment counter based on observation time, idY and idZ are tied to specific observations within a given set, and are integer counters that vary from 0-150 or so. Is there a way to do this with one (or more) sql queries, or would I be better off writing a specific program for doing the selection? Thanks for the help! ken === "Diplomacy is the weapon of the Civilized Warrior" - Hun, A.T. Ken Gieselman [EMAIL PROTECTED] System Administrator http://www.endlessknot.com/~kgieselm === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: subquery help for an update
Found some help here: http://forums.mysql.com/read.php?10,10572,11064#msg-11064 UPDATE Table1 SET Table1.Field1 = ( SELECT count(*) FROM Table2 WHERE Table2.Code2=Table1.Code1 ) and that query works for me, great!! But I still don't understand why it updates properly without the WHERE clause. So if anybody has a link to a doc for subqueries? From: "mel list_php" <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Subject: subquery help for an update Date: Wed, 16 Feb 2005 14:32:48 + Hi, I have one table tableNames ID,name, new_name. The columns ID and name are populated. I would like to update the field new_name from an other table tempName (ID,nameUpdated). The ID between the 2 tables are the same, but I don't have the info for all the ID. (so 568 rows in my first table only 550 in my second one) I saw in the MySQL cookbook different solutions, creating a new table from joining the 2 firsts, writing a script to do thisBut this book also says that with the subquery in MySQL 4.1 it should be possible through a direct query. As I updated to 4.1, I would like to give it a try. Here is what I want to do: update tableName set new_name= (select nameUpdated from tableName,tempName where tableName.ID=tempName.ID) First I have here the subquery error detailed in the manual: "UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1); It's okay to use a subquery for assignment within an UPDATE statement, since subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table, in this case table t1, for both the subquery's FROM clause and the update target. " and second how can I specify update for each ID? I tried the same with insert into tableName select nameUpdated from tableName, tempName where tableName.ID=tempName.ID, but of course the records are inserted at the end of the first table. I tried to find some help from the manual but saw nothing, and I think this is a common problem (as mentionned in the cookbook) but I can't find any solution and I'm becoming crazy trying to combine that 2 tables!!! If somebody could help on this query, but also give me some pointer to a good doc/book regarding subqueries? Thanks a lot for any help. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
subquery help for an update
Hi, I have one table tableNames ID,name, new_name. The columns ID and name are populated. I would like to update the field new_name from an other table tempName (ID,nameUpdated). The ID between the 2 tables are the same, but I don't have the info for all the ID. (so 568 rows in my first table only 550 in my second one) I saw in the MySQL cookbook different solutions, creating a new table from joining the 2 firsts, writing a script to do thisBut this book also says that with the subquery in MySQL 4.1 it should be possible through a direct query. As I updated to 4.1, I would like to give it a try. Here is what I want to do: update tableName set new_name= (select nameUpdated from tableName,tempName where tableName.ID=tempName.ID) First I have here the subquery error detailed in the manual: "UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1); It's okay to use a subquery for assignment within an UPDATE statement, since subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table, in this case table t1, for both the subquery's FROM clause and the update target. " and second how can I specify update for each ID? I tried the same with insert into tableName select nameUpdated from tableName, tempName where tableName.ID=tempName.ID, but of course the records are inserted at the end of the first table. I tried to find some help from the manual but saw nothing, and I think this is a common problem (as mentionned in the cookbook) but I can't find any solution and I'm becoming crazy trying to combine that 2 tables!!! If somebody could help on this query, but also give me some pointer to a good doc/book regarding subqueries? Thanks a lot for any help. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Correlated subquery help
Hi Dan- Thx for responding. And yes, I think you're absolutely correct. Let me update that query - should look like: select a.k1, a.k2, a.total_amt from Z a where a.total_amt in (select b.total_amt from Z b where b.k1 = a.k1 order by b.total_amt desc limit 10) order by a.k1, a.total_amt desc ; Anyone else? Thanks, R -Original Message- From: Dan Sashko [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 01, 2004 9:49 PM To: Mysql Subject: Re: Correlated subquery help isn't the where subquery would always return only one record if set of (k1,k2) is a primary key? I dont have 4.1+ installed to test on but if you remove 'limit 10' and run it don't you get the same list as if you ran 'select k1,k2,total_amt from Z' ? - Original Message - From: "Rick Robinson" <[EMAIL PROTECTED]> To: "Mysql" <[EMAIL PROTECTED]> Sent: Wednesday, December 01, 2004 6:08 PM Subject: Correlated subquery help > Hi all- > I'm using MySQL 4.1.7, trying to do a subquery that's apparently > unsupported - > I'm hoping someone can provide a quick alternative for me. > > I have a simple table Z with 3 columns, k1, k2, and total_amt, where k1 > and k2 > make up the primary key. I want to create a report that lists the the top > 10 > total_amt for each k1, k2. My original query was going to be of the form: > > select >a.k1, >a.k2, >a.total_amt > from Z a > where a.total_amt in >(select b.total_amt > from Z b > where b.k1 = a.k1 and > b.k2 = a.k2 >order by b.total_amt desc >limit 10) > order by a.k1, a.total_amt desc > ; > > But I get the dreaded error: ERROR 1235: This version of MySQL doesn't yet > support 'LIMIT & IN/ALL/ANY/SOME subquery' > > Is there a better way to do this query? > > Thanks for your help. > Regards, > R > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Correlated subquery help
isn't the where subquery would always return only one record if set of (k1,k2) is a primary key? I dont have 4.1+ installed to test on but if you remove 'limit 10' and run it don't you get the same list as if you ran 'select k1,k2,total_amt from Z' ? - Original Message - From: "Rick Robinson" <[EMAIL PROTECTED]> To: "Mysql" <[EMAIL PROTECTED]> Sent: Wednesday, December 01, 2004 6:08 PM Subject: Correlated subquery help Hi all- I'm using MySQL 4.1.7, trying to do a subquery that's apparently unsupported - I'm hoping someone can provide a quick alternative for me. I have a simple table Z with 3 columns, k1, k2, and total_amt, where k1 and k2 make up the primary key. I want to create a report that lists the the top 10 total_amt for each k1, k2. My original query was going to be of the form: select a.k1, a.k2, a.total_amt from Z a where a.total_amt in (select b.total_amt from Z b where b.k1 = a.k1 and b.k2 = a.k2 order by b.total_amt desc limit 10) order by a.k1, a.total_amt desc ; But I get the dreaded error: ERROR 1235: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' Is there a better way to do this query? Thanks for your help. Regards, R -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Correlated subquery help
Hi all- I'm using MySQL 4.1.7, trying to do a subquery that's apparently unsupported - I'm hoping someone can provide a quick alternative for me. I have a simple table Z with 3 columns, k1, k2, and total_amt, where k1 and k2 make up the primary key. I want to create a report that lists the the top 10 total_amt for each k1, k2. My original query was going to be of the form: select a.k1, a.k2, a.total_amt from Z a where a.total_amt in (select b.total_amt from Z b where b.k1 = a.k1 and b.k2 = a.k2 order by b.total_amt desc limit 10) order by a.k1, a.total_amt desc ; But I get the dreaded error: ERROR 1235: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' Is there a better way to do this query? Thanks for your help. Regards, R
Re: Subquery help...
On Sat, 5 Jun 2004 21:10:42 -0600 "Daniel Isenhower" <[EMAIL PROTECTED]> wrote: > > First off, I assume you are using a version of mysql able to > > handle > sub-queries. 4.1 or 5.0 (4.0.xx does NOT support sub-queries) > > Ugh... I feel dumb :) I'm using 4.0 No worries, there are too many versions of MySQL to choose from... > > FWIW, this is an easy query with a JOIN: > > > > SELECT id FROM work w > > INNER JOIN client_list cl ON cl.id = w.client_id > > WHERE cl.name = 'Some Company'; > > > > Just in case you are using mysql 4.0 or earlier... > > Thanks very much! This is what I need :) > > Also, while I'm at it, any book recommendations for getting to know > MySQL better? I've always liked the reference manual, but it's essentially a reprint of the online documentation, which is excellent. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subquery help...
> First off, I assume you are using a version of mysql able to handle sub-queries. 4.1 or 5.0 (4.0.xx does NOT support sub-queries) Ugh... I feel dumb :) I'm using 4.0 > FWIW, this is an easy query with a JOIN: > > SELECT id FROM work w > INNER JOIN client_list cl ON cl.id = w.client_id > WHERE cl.name = 'Some Company'; > > Just in case you are using mysql 4.0 or earlier... Thanks very much! This is what I need :) Also, while I'm at it, any book recommendations for getting to know MySQL better? Thanks again :-) -Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subquery help...
On Sat, 5 Jun 2004 19:48:44 -0600 "Daniel Isenhower" <[EMAIL PROTECTED]> wrote: > This is my first email to the list, so be nice ;-) Welcome, this is a very helpful list... > I'm having some difficulty with a subquery that I'm trying to do, > and was wondering if anyone here can shed some light on the issue... First off, I assume you are using a version of mysql able to handle sub-queries. 4.1 or 5.0 (4.0.xx does NOT support sub-queries) > This query returns a result as expected: > SELECT id FROM client_list WHERE name="Some Company" > (the id returned here is 3) > > This query also returns a result as expected: > SELECT id FROM work WHERE client_id='3' ORDER BY id DESC; > > Does anyone know why this one doesn't return any results? > SELECT id FROM work WHERE client_id='(SELECT id FROM client_list > WHERE name="Some Company")' ORDER BY id DESC; Don't use quotes, it's looking for a client_id that is literally the stuff inside your quotes. I'm guessing that'll never be the case. :) Try: SELECT id FROM work WHERE client_id = (SELECT id FROM client_list WHERE name="Some Company") ORDER BY id DESC; FWIW, this is an easy query with a JOIN: SELECT id FROM work w INNER JOIN client_list cl ON cl.id = w.client_id WHERE cl.name = 'Some Company'; Just in case you are using mysql 4.0 or earlier... Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subquery help...
This is my first email to the list, so be nice ;-) I'm having some difficulty with a subquery that I'm trying to do, and was wondering if anyone here can shed some light on the issue... This query returns a result as expected: SELECT id FROM client_list WHERE name="Some Company" (the id returned here is 3) This query also returns a result as expected: SELECT id FROM work WHERE client_id='3' ORDER BY id DESC; Does anyone know why this one doesn't return any results? SELECT id FROM work WHERE client_id='(SELECT id FROM client_list WHERE name="Some Company")' ORDER BY id DESC; Thanks for any help :) -Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subquery Help
I'm about to pull my hair out on this one so I thought I would see if somebody could point me in the right direction. I have a subquery that like like so. SELECT * FROM Word INNER JOIN DomainWord USING ( word ) INNER JOIN Domain USING ( domain ) WHERE Domain.domain = ANY( SELECT Domain.domain FROM Word INNER JOIN DomainWord USING ( word ) INNER JOIN Domain USING ( domain ) WHERE Word.word = 'php' ) AND parked = 'N' And the results are below. Only problem is that I only want the domain back if pvr for all entries is Y. If there are any N's I don't want the domain back. +-++-+-++--- --++---++-+ | word| word_count | updated | pvr | domain | word | domain | words | parked | updated | +-++-+-++--- --++---++-+ | extreme |108 | 2004-02-25 13:07:21 | Y | extremephp.org | extreme | extremephp.org | 5 | N | 2004-02-25 11:48:41 | | mep |187 | 2004-02-25 11:48:41 | Y | extremephp.org | mep | extremephp.org | 5 | N | 2004-02-25 11:48:41 | | php | 26 | 2004-02-25 13:10:53 | Y | extremephp.org | php | extremephp.org | 5 | N | 2004-02-25 11:48:41 | | php | 26 | 2004-02-25 13:10:53 | Y | phpiba.com | php | phpiba.com | 3 | N | 2004-02-25 13:10:53 | | hpib| 3 | 2004-02-25 13:10:53 | N | phpiba.com | hpib | phpiba.com | 3 | N | 2004-02-25 13:10:53 | | iba | 66 | 2004-02-25 13:10:58 | Y | phpiba.com | iba | phpiba.com | 3 | N | 2004-02-25 13:10:53 | +-++-+-++--- --++---++-+ Any ideas? Donny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
