Results 1 to 11 of 11

Thread: Multiple group columns ..

  1. #1
    Join Date
    May 2003
    Posts
    3

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

  2. #2
    Join Date
    May 2003
    Posts
    4
    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.

  3. #3
    Join Date
    May 2003
    Posts
    3
    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 ..

  4. #4
    Join Date
    May 2003
    Posts
    1
    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

  5. #5
    Join Date
    May 2003
    Posts
    4
    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.

  6. #6
    Join Date
    May 2003
    Location
    Indonesia
    Posts
    7

    Question

    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)

  7. #7
    Join Date
    May 2003
    Posts
    4

    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.

  8. #8
    Join Date
    May 2003
    Posts
    3
    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

  9. #9
    Join Date
    Feb 2003
    Location
    Johannesburg, South Africa
    Posts
    145
    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

  10. #10
    Join Date
    May 2003
    Posts
    4

    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.

  11. #11
    Join Date
    Feb 2003
    Location
    Johannesburg, South Africa
    Posts
    145
    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
  •