) 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 (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL);
LOCK TABLES shop read;
INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;
SELECT shop.article, dealer, shop.price FROM shop, tmp WHERE
shop.article=tmp.article AND shop.price=tmp.price;
UNLOCK TABLES;
DROP TABLE tmp;
"Can it be done with a single query?"
Yes, but only by using a quite inefficient trick that I call the "MAX-CONCAT
trick":
SELECT article,
SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer,
0.00+LEFT( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price
FROM shop
GROUP BY article;
----- Original Message -----
From: "Mark Wilson" <[EMAIL PROTECTED]>
To: "Mysql Mailing List" <[EMAIL PROTECTED]>
Sent: Wednesday, October 08, 2003 9:35 AM
Subject: Easy (?) conditional SELECT
> I have an app for which people can submit plans.
> Each plan relates to a particular product.
> A new plan can be submitted for the same product, so each plan has its own
> submission number. (1,2,3...)
> Each plan is composed of artifacts.
> The (artifacts) table looks like this:
> artifact_id INT
> product_id INT
> plan_submission_number INT
> (etc)
>
> Task: get all the items for the most recent (i.e., highest) submission
plan for
> a particular product.
>
> Since I'm relatively new to MySQL, and haven't mastered much beyond the
most
> basic SELECTs, much less JOINs, I'm not sure how to do this. I think the
> following should work (for product_id = 1), but it returns a syntax error.
>
> SELECT *
> FROM `artifacts`
> WHERE ( product_id = '1' AND plan_submission_number = (
> SELECT MAX( plan_submission_number )
> FROM 'artifacts'
> WHERE product_id = '1' ) )
> ---------------
> Error message:
> 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 'SELECT MAX(
> plan_submission_number )
> FROM 'artifacts'
> WHERE p
> ---------------
> What am I missing? Thanks....
>
> - Mark
>
> --
> Mark Wilson, Computer Programming Unlimited (cpuworks.com)
> Web : http://cpuworks.com Tel: 410-549-6006
> Email: [EMAIL PROTECTED] Fax: 410-549-4408
>
>
>
>
>
> --
> 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]
