You cant have subqueries (select from where) in the where clause.
On Wed, 2003-10-08 at 15:00, Mark Wilson wrote:
> BTW, I tested the SELECT(MAX) part of this separately, and discovered that MySQL
> doesn't like the single quotes around the table name, so I took them out.
> Now THIS query works:
>
Try the following:
SELECT a.*
FROM artifacts a left join artifacts higher
on a.product_id = higher.product_id
and higher.plan_submission_number > a.plan_submission_number
WHERE a.product_id = '1'
AND higher.product_id is null;
For anyone following this, I'm using some sample code,
Subqueries don't show up until v4.1 which I have running on one of my servers. Runs
great and I have it into production.
--ja
On Wed, 8 Oct 2003, Rory McKinley wrote:
> Problem 1 : Your query contains a subquery :
> SELECT MAX( plan_submission_number )
> > FROM 'artifacts'
> > WHERE product_
Problem 1 : Your query contains a subquery :
SELECT MAX( plan_submission_number )
> FROM 'artifacts'
> WHERE product_id = '1' )
subqueries are (AFAIK) not supported in the latest production version of
MySQL (4.0.15) but are coming soon...might already be in a beta ver
Two possible solutions -
On Wed, 2003-10-08 at 15:06, Mark Wilson wrote:
> Er, I _used_ a "basic SQL tutorial", which specifically said that this should
> work. The problem seems to be a limitation of MySQL, not general SQL operation.
> That being said, are there any clever one-query options (using JOINs, etc?) or
> is th
Er, I _used_ a "basic SQL tutorial", which specifically said that should work.
The problem seems to be a limitation of MySQL, not general SQL operation.
That being said, are there any clever one-query options (using JOINs, etc?) or
is this basically a 2-step process in MySQL?
--
Mark Wilson, Compu
BTW, I tested the SELECT(MAX) part of this separately, and discovered that MySQL
doesn't like the single quotes around the table name, so I took them out.
Now THIS query works:
SELECT MAX( plan_submission_number )
FROM artifacts
WHERE product_id = '1'
-- (returns '2')
But this one still doesn't:
) In MySQL we don't have subselect. this is not valid:
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);
instead of this, in MySQL we can write:
CREATE TEMPORARY TABLE tmp (
