-
Multiple group columns ..
Say I have a table looking like this:
http://www.mysql.com/doc/en/Examples.html
And I want to see the highest price per article:
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article
That would work just fine, but what if I want to see who the dealer is ? If I try to just insert 'dealer' in the SELECT statement, it won't show me the right dealer, just the first one it finds in that particular group (like this:)
SELECT article, MAX(price) AS price, dealer
FROM shop
GROUP BY article
How do I overcome this?
Last edited by stenkross; 05-23-2003 at 03:52 AM.
-
Isn't what you want in the rest of that article (this page)? If it isn't, then I am unsure what you are asking.
-
Originally posted by aus
Isn't what you want in the rest of that article (this page)? If it isn't, then I am unsure what you are asking.
That's correct, or at least is seem like what I'm looking for, but when I try the following query (from their example):
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);
I get the following error:
ERROR 1064: You have an error in your SQL syntax near 'SELECT MAX(s2.price) FROM shop s2 WHERE s1.article =' at line 3
(I'm using MySQL 3.23.55)
And I have no idea what could be wrong ..
-
The SQL query you are trying to run includes a sub query (or nested query).
Sub queries are only supported by MySQL version 4.1 or newer.
/Tom
-
You are using a version that does not support subqueries. Anyway, I got to thinking, that query would only return the article and dealer of the priciest article. You need a more complicated query.
Create a temporary table that contains two columns:
CREATE TEMPORARY TABLE tmpshop (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
);
Then execute this query:
INSERT INTO tmpshop SELECT article, MAX(price)
FROM shop GROUP BY article;
Now you can join the two tables and get the article, price and dealer:
SELECT a1.article, a1.price, a2.dealer
FROM tmpshop a1, tmpshop a2
WHERE a1.article = a2.article
AND a1.price = a2.price;
This query will return what you want, but be careful because it will return multiple rows for an article if the dealers have the same price.
-
I am beginer in MySQL. The problems is mysql doesn't support sub select
how about:
SELECT article, price, dealer
FROM shop having max(price)
-
Not quite
The HAVING clause on you query needs to be:
HAVING price = MAX(price)
But this will not do what we are looking for. This will only return the most expensive article and its dealer.
-
Well, I'm a big fan of simple solutions And the simplest thing to do now, is to wait for a stable release wich supports subselect. The current 4.1 is still only a alpha release ..
Big thanx for your help
-
Code:
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;
Source : http://www.mysql.com/doc/en/example-...group-row.html
Result :
Code:
mysql> 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;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+---------+--------+-------+
4 rows in set (0.00 sec)
The above was tested on Mandrake 9 running MySQL 4.0.11a AND on redHat 6.2 running MySQL 3.23.49
Cheers
-
There is an easier way
Thanks to Csaba Gabor in the messages following the tutorial on the page listed in the previous post, there is a really simple (and quite efficient way to do it). Use the query:
SELECT shop.*
FROM shop LEFT JOIN shop s2
ON shop.article = s2.article AND shop.price < s2.price
WHERE s2.article IS NULL
This is a much more elegant solution that uses a self join to limit the results. Thanks Csaba.
-
Indeed a much more cleaner solution
Cheers
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|