# Database Discussions > MySQL >  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  :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  :Smilie:

----------


## 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.  :Smilie:

----------


## nicc777

Indeed a much more cleaner solution  :Smilie: 

Cheers

----------

