Multiple group columns ..

[stenkross]

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?

[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.

[stenkross]

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 ..

[tskou]

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

[aus]

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.

[iferianto]

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)

[aus]

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.

[stenkross]

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

[nicc777]

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 :

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

[aus]

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.

[nicc777]

Indeed a much more cleaner solution

Cheers