Awesome, thanks. My first attempt is able to run on the full table in
85 seconds. I'll continue to read up on these queries to see if I can
optimize it further.
Thanks again.
Peter Brawley wrote:
Kip,
> What can I do to optimize this query?
For more efficient alternatives see "Within-gr
Kip,
> What can I do to optimize this query?
For more efficient alternatives see "Within-group aggregates" at
http://www.artfulsoftware.com/queries.php.
PB
-
Kip Turk wrote:
I'm having problems optimizing a series of subselects. I have the
following sample table:
mysql> select * fr
http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/
Is a good article.
-Sheeri
On 4/21/06, Chris White <[EMAIL PROTECTED]> wrote:
I was looking around the list search and didn't find much on this subject
(maybe didn't look back far enough), but I was discussing
On 6/17/05, [EMAIL PROTECTED] wrote:
>
> There is one caveat: It is not currently possible to modify a table and select
> from the same table in a subquery.
That is not the only problem: there is no guarantee the subquery will
only return one record. So even if MySQL wouldn't have this limitation
Thanks for the reply.
What do you mean by 'self join'?
>>> Eric Bergen <[EMAIL PROTECTED]> 6/17/05 4:39:28 PM >>>
Could you accomplish this with an update and self join?
Ed Reed wrote:
>Sorry, I thought it was easy to understand.
>
>I wanna update a field in a table with a value from the s
Thanks
>>> <[EMAIL PROTECTED]> 6/17/05 2:03:02 PM >>>
Hi,
There is one caveat: It is not currently possible to modify a table and select
from the same table in a subquery.
this phrase is from
http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html
solution
*
create table t as S
Could you accomplish this with an update and self join?
Ed Reed wrote:
Sorry, I thought it was easy to understand.
I wanna update a field in a table with a value from the same field but from a different record of the same table.
For example, using the query in my original message,
+
Sorry, I thought it was easy to understand.
I wanna update a field in a table with a value from the same field but from a
different record of the same table.
For example, using the query in my original message,
+--+
|RecID |FIELD1 |FIELD2 |
+---
Hi,
There is one caveat: It is not currently possible to modify a table and select
from the same table in a subquery.
this phrase is from
http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html
solution
*
create table t as Select field1 From table1 Where field2="Some
Value"
Upda
"Ed Reed" <[EMAIL PROTECTED]> wrote on 06/17/2005 01:35:40 PM:
> Can anyone tell me how I can make this work or suggest a work around?
> Update table1 Set field1=(Select field1 From table1 Where field2="Some
Value")
> Where field2 ="Another Value";
> Thanks
OK, your example must be broken (or
rning multiple rows in only one col to an aggregate function.
Thanks and bye,
Bernhard
- Original Message -
From: "Victoria Reznichenko" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, February 19, 2004 2:34 PM
Subject: Re: Subselect in aggregate function in My
[EMAIL PROTECTED] wrote:
>
> Hi,
>
> I have some logging data from a webserver in a table and want to do some
> analysis.
> I infact want to see how many files are requested at one time.
> To do this I
> SELECT COUNT(time) anz FROM table GROUP BY time ORDER BY anz DESC
> This gives me the number
At 2:28 PM -0500 11/17/03, Peter J. Krawetzky wrote:
Could someone please tell me what is wrong with this query:
select * from personnel where dept_id not in (select dept_id from
attendance_respond)
I keep getting a 1024 error message. It looks pretty straightforward to me.
I am running 4.0.16 o
First of all, you query is not correct and this is not a subselect query.
you can try this:
SELECT Salary, Level, Department FROM SalaryInfo ORDER BY Salary DESC LIMIT
1;
second of all, for subselect MySQL 4.1 can help you.
Mojtaba
- Original Message -
From: "Prashant Pai" <[EMAIL PROT
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Tuesday 22 July 2003 22:28, [EMAIL PROTECTED] wrote:
> Sorry I completely forget the query:
> SELECT * FROM Kardex WHERE Fecha IN (SELECT maxfecha FROM fechas)
> P.D. Im using 4.1 alpha version for win32
OK, something is still missing. I went to
-
From: Michael Satterwhite [mailto:[EMAIL PROTECTED]
Sent: Martes, 22 de Julio de 2003 06:05 a.m.
To: [EMAIL PROTECTED]
Subject: Re: Subselect in 4.0.12-max with --new option / Max Date
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Tuesday 22 July 2003 00:04, [EMAIL PROTECTED] wrote:
>
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Tuesday 22 July 2003 00:04, [EMAIL PROTECTED] wrote:
> You are right above the max date query and I really appreciate your
> help, but the subselect query its other question. I try the subselect
> query with the 4.0.12-max win32 with the --new optio
At 1:04 -0400 7/22/03, [EMAIL PROTECTED] wrote:
You are right above the max date query and I really appreciate your
help, but the subselect query its other question. I try the subselect
query with the 4.0.12-max win32 with the --new option in the command
line to start the server, according to the m
feature? I missing something
about the conf?? maybe my.ini??
-Original Message-
From: Michael Satterwhite [mailto:[EMAIL PROTECTED]
Sent: Lunes, 21 de Julio de 2003 10:54 p.m.
To: [EMAIL PROTECTED]
Subject: Re: Subselect in 4.0.12-max with --new option / Max Date
-BEGIN PGP SIGNED MESSAGE
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Monday 21 July 2003 18:48, [EMAIL PROTECTED] wrote:
> Cod Product date
> 10Nissan Car 2003-06-20
> 20Guitar Yamaha 2003-07-01
> 10Nissan Car 2003-05-01
> 30Stereo Cables 2003-01-20
> 20Gui
product, MAX(date) FROM table GROUP BY cod
And other combination using the HAVING syntax.
Please help!!
-Original Message-
From: Michael Satterwhite [mailto:[EMAIL PROTECTED]
Sent: Sábado, 19 de Julio de 2003 08:36 p.m.
To: [EMAIL PROTECTED]
Subject: Re: Subselect in 4.0.12-max with --new
[EMAIL PROTECTED] wrote:
>
> I was reading the manual and it said that the subselect is only
> available in 4.1 or using the 4.0.12 with the mysqld ?new command line
> to start it.
>
> But it doesn?t working!! So I downloaded the 4.1 alpha version with the
> same problem. The error is:
>
> ERROR
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Friday 18 July 2003 21:28, [EMAIL PROTECTED] wrote:
> ERROR 1064: You have an error in your SQL syntax. Check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near
.
>
> Any ideas? I need subselect working.
Ben Margolin <[EMAIL PROTECTED]> wrote:
>
> I am new to subselects, and what I really want is to do them in an update,
> but the following shows a simpler select, that also doesn't seem to work as I
> think it should. Advice? Do I just misunderstand how subselects are actually
> executed?
>
> (Th
These are not real subselects. You speak of a subselect when you are
define a select in the FROM clause of a kwiri. If you define them in the
WHERE clause, you speak of derived tables. Putting them even into the
SELECT clause is something specific to MySQL (as far as I know).
Ok, now this is theor
Thanks Mike for the response,
I didnt even think for a moment that CREATE... SELECT, INSERT ...SELECT etc.
wouldnt be a subselect command (also it is a nested query).
I dont know why I didnt think about that. Mike you mentioned a very good
point. Maybe I was thinking to focused to see the whol
I am not sure that could be viewed as a subselect, as it in not a SELECT
within an SELECT, but is instead a SELECT within a CREATE. I would
imagine that the SELECT within the CREATE is easier to implement that
the actual SELECT within a SELECT.
Regards,
Mike Hillyer
www.vbmysql.com
> -Origin
Hi Egor,
Thank you for the reply. Do you mind to go abit mor into details ? It seems
that I got domething wrong here.
If nested queries are not only subselects, which other nested queries exist
there ?
Do you mind just writing a few samples, please ?
Sorry if this seems a simple question - fr
"Nils Valentin" <[EMAIL PROTECTED]> wrote:
>
> I have a question regarding the MySQL feaures.
>
> From Version 4.1 Full subselect support was/is announced.
>
> However if I understood correctly then already from Version3.23-41 (or
> earlier) there are some subselect functions already included.
At 21:34 -0800 3/17/03, geeta varu wrote:
does subselect work in mySQL 3.23 ...i'm trying to
exceute the following query ...
No subselects until 4.1. Perhaps you can rewrite your query as a join.
http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html
Select can_ID from TABLE1 where (designation
On Friday 21 February 2003 09:16, kk wrote:
> You may have to wait until version 4.1 if I am not mistaken.
You are not mistaken. Subselects will come in 4.1
>
> - Original Message -
> From: "geeta varu" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Friday, February 21, 2003 4:09
You may have to wait until version 4.1 if I am not mistaken.
- Original Message -
From: "geeta varu" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, February 21, 2003 4:09 PM
Subject: subselect
> does mysql version 3.23 support subqueries ...if no
> does latest version 4.0 sup
>Student | Class
>--
>Steve Lefevre | Math101
>Stacy Adams | Intro to SQL
>Something like
>SELECT Student.Name, Classes.Name FROM Students, Classes WHERE
>Students.StudentID = " . $ID . " AND Classes.Name IN ( SELECT
Classes.Name
>FROM Classes WHERE ClassID = Students.Clas
From: Lefevre, Steven <[EMAIL PROTECTED]>
> ...I think I need a subselect...
It's rare that a sub-select is actually ~needed~, but it does happen. You can almost
always get around it with a JOIN of some sort.
> Final result should look like
> Student | Class
> --
> St
I think the problem is in your table structure. If you did this:
Students:
- Name
- StudentID
Classes:
- ClassID
- Name
StudentsClasses:
- StudentID (PK)
- ClassID (PK)
(You make them a combined key by doing PRIMARY KEY (StudentID,ClassID) in
your table def.)
When you add a student to a class
-Original Message-
From: Lefevre, Steven [mailto:[EMAIL PROTECTED]]
>
> Hey folks-
>
> 'nother question.
> I'm not an SQL expert, and I think I need a subselect, which
> means I need a workaround on MySQL 3.23 or whatever version it is.
>
> Here's the tables I have, with the relevant column
On Tuesday 10 December 2002 07:48, Amittai Aviram wrote:
> Sorry! I meant UPDATE, not INSERT!
>
> UPDATE administrators SET id =
> (SELECT id FROM faculty
> WHERE lastName = "Jones")
> WHERE ordr = 1;
You can use multi-tables updates from 4.0.2
In earlier versions use programming languages.
OOPS! Please note correction below!
> Here is what I would want to do:
>
> INSERT INTO administrators (id) values
> (SELECT id FROM faculty
> WHERE lastName = "Jones")
> WHERE ordr = 1;
Sorry! I meant UPDATE, not INSERT!
UPDATE administrators SET id =
(SELECT id FROM faculty
WHERE lastName =
Eric,
Wednesday, October 23, 2002, 2:06:30 PM, you wrote:
EG> Im a new user on this list as well as Mysql.
EG>
EG> I use it with coldfusion
EG>
EG> My first problem is the following :
EG>
EG> I have a table where I created a timestamp column named
EG> date_creation
EG>
EG>
Eric Guesdon wrote:
> Hi everyone,
>
> Im a new user on this list as well as Mysql.
>
> I use it with coldfusion
>
> My first problem is the following :
>
> I have a table where I created a timestamp column named
> date_creation
>
> When I try to execute this sql
vinita,
Thursday, October 03, 2002, 5:56:49 AM, you wrote:
vvM> mysql> select userGroup from machineGroup_userGroups where hostname="mulga";
vvM> +---+
vvM> | userGroup |
vvM> +---+
vvM> | staff |
vvM> | tech |
vvM> +---+
vvM> 2 rows in set (0.01 sec)
vvM> mysql>
daniel,
Thursday, July 11, 2002, 3:37:07 AM, you wrote:
d> why is it that this doens't work?
Because your syntax is wrong. If you want to use INSERT .. SELECT
statement, please, look at the manual for correct syntax:
http://www.mysql.com/doc/I/N/INSERT_SELECT.html
d> INSERT INTO myta
MySQL, as of yet, doesn't support sub-selects !
-Message d'origine-
De : daniel [mailto:[EMAIL PROTECTED]]
Envoyé : jeudi 11 juillet 2002 02:37
À : [EMAIL PROTECTED]
Objet : subselect in an insert
why is it that this doens't work?
INSERT INTO mytable (column0, column1, column2, column3,
Wouldn't "select table1.id from table1, table2 where table1.id !=
table2.t1id and table2.id = 9" work? Joins don't have to include output
columns from both tables, but columns from both tables can (and should,
unless you want cartesian products) appear in the WHERE clause.
-Mark
- Origina
I believe the following query will do what you want it to do:
SELECT t1.id from table1 as t1 LEFT JOIN table2 as t2 on t2.t1id = t1.id
AND t2.id = 9 WHERE t2.id IS NULL;
More information can be found at
http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html about sub-select
work arounds.
Hop
What are you trying to accomplish with the query? What results do you
EXPECT to be returned?
Can you not do what you're trying to do with:
SELECT id FROM Table WHERE (nr=3D123 AND nr=3D456)
?
- Original Message -
From: "Spielberg Micha" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent:
http://www.mysql.com/documentation/mysql/bychapter/manual_Introduction.html#ANSI_diff_Sub-selects
On Monday 15 April 2002 2:49 pm, Jeff Shipman wrote:
> Yeah, that nasty thing with temporary tables and
> such? Or am I looking at the wrong page? If you
> could point me in the right direction, I'd
Yeah, that nasty thing with temporary tables and
such? Or am I looking at the wrong page? If you
could point me in the right direction, I'd appreciate
it (slow modem connection, etc).
Christopher Thompson wrote:
> Read the manual. MySQL doesn't support subselects but offers you
> alternatives.
Read the manual. MySQL doesn't support subselects but offers you
alternatives.
On Monday 15 April 2002 2:45 pm, Jeff Shipman wrote:
> I'm trying to do a fairly simple select of a
> column from a table where the date of that column
> happens to be the highest value. I tried this:
>
> SELECT name
On Tue, Mar 19, 2002 at 12:45:57AM -, Alex Speed wrote:
> Ok, no support for it (d'oh), so, how do I go about breaking it down into a
> join or something like that?
>
> - Original Message -
> From: "Alex Speed" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, March 19, 2
Ok, no support for it (d'oh), so, how do I go about breaking it down into a
join or something like that?
- Original Message -
From: "Alex Speed" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, March 19, 2002 12:13 AM
Subject: SubSelect Problem
> SELECT textid, title, descript
Hi Alex,
MySQL doesn't support sub-selects yet.
Gurhan
-Original Message-
From: Alex Speed [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 18, 2002 7:13 PM
To: [EMAIL PROTECTED]
Subject: SubSelect Problem
SELECT textid, title, description from structure where textid in (select
subtopic
Alex,
I believe you'll find that MySQL does not support subselects. This is
why you're getting the error message. I suggest you consult the SELECT
syntax manual page (http://www.mysql.com/doc/S/E/SELECT.html) to see
what you can do with selects.
You could try to convert this into a join, or
Perhaps I am missing something right in front of me, or I have not been in
the loop, but it is my understanding that MySQL does not support
sub-selects, yet.
http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html
BTW, you may receive lots of replies of this very nature, because it is
something ev
>
>database,sql,query,table
>Can someone please tell me why this won't work.
>
>if (defined($res)){
> my $stmt = qq{select distinct symbol from portfolio where $res};
> my $sth = $dbh->prepare($stmt);
> $sth->execute();
>
> my $portsymbols = "";
>
> while ($sth->fetchrow) {
> $portsymb
Hi Paul,
There is no direct way to make a string by concatinating strings of
different columns. But your problem can be solved in a different way.
select distinct p.symbol, i.name from portfolio p, portfolio pp, stockinfo i
where pp.type = '401k' AND pp.owner = 'jim'
and p.symbol = i.symbol a
Is this too simple?
select distinct p.symbol, i.name from portfolio p, stockinfo i
where p.symbol = i.symbol
and p.type = '401k'
and p.owner='jim'
order by p.symbol
-
Tom Haapanen -- Software Metrics/Equitrac Corp.
Advanced Printing Solutions -- http://www.metrics.com/
-Original Me
57 matches
Mail list logo