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?

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.

Quote:

---

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)

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 :cool: 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

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