Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161

    Unanswered: DISTINCT with MAX

    Hi

    I want to get the maximum Price for every Item. There can be many same Items with different price.

    This works :
    Code:
    SELECT `Item`, `Price`
    FROM `table1` t1
    WHERE
    `Price` = (SELECT MAX(`Price`) FROM `table1` WHERE `Item` = t1.`Item`)
    ORDER BY `Price` DESC
    LIMIT 0,10;
    Though I was wondering if theres a way to get it done this way
    Code:
    SELECT `Item`, (SELECT MAX(`Price`) FROM `table1` WHERE `Item` = t1.`Item`) AS `Price`
    FROM `table` t1
    WHERE `Position` = '1 - 3'
    ORDER BY Price DESC
    LIMIT 0,10;
    Problem is, this 2nd one returns duplicate Items - how do I get DISTINCT `Items` ?
    I understand DINSTINCT a, b, c is equivalent to DINSTINCT a, DINSTINCT b, DINSTINCT c

    Thanks
    MySQL 5.1

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anjanesh
    Though I was wondering if theres a way to get it done this way
    no, not that way
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Code:
    SELECT DISTINCT `Item`,
    (SELECT MAX(`Price`) FROM `table1` WHERE `Item` = t1.`Item`) AS `MaxPrice`
    FROM `table` t1
    ORDER BY `MaxPrice` DESC
    LIMIT 0,10;
    did seem to return the same result set - except it took 4 times longer !
    Price is of type float(5,2) I normally dont create an index on non-textual columns. Will creating an index on Price speed up the process ?

    Also, (Item, Price) isnt unique.
    So what if there are same Items with same Price ?
    Code:
    SELECT `Item`, `Price`
    FROM `table1` t1
    WHERE
    `Price` = (SELECT MAX(`Price`) FROM `table1` WHERE `Item` = t1.`Item`)
    ORDER BY `Price` DESC
    LIMIT 0,10;
    can return duplicate rows.
    Last edited by anjanesh; 12-19-07 at 03:46.
    MySQL 5.1

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Take a look at GROUP BY
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    I did. Im aware of the consequences when using GROUP BY for LARGE sets of data.
    Is this equivalent to GROUP BY ?
    Code:
    SELECT DISTINCT `Item`, `Price`
    FROM `table1` t1
    WHERE
    `Price` = (SELECT MAX(`Price`) FROM `table1` WHERE `Item` = t1.`Item`)
    ORDER BY `Price` DESC
    How is DISTINCT `Item`, `Price` interpreted as?
    MySQL 5.1

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if an item can have multiple prices, and you want only the maximum price for each item, abandon all hope of getting DISTINCT to provide your solution

    DISTINCT item,price will provide one row for every item,price combination

    GROUP BY is a good solution, but then you will get only the maximum price per item, and no other columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Code:
    a,4
    a,5
    a,8
    a,4
    b,2
    b,9
    b,8
    b,5
    b,8
    b,1
    c,3
    c,9
    c,4
    c,3
    c,5
    SELECT DISTINCT `Item`, `Price` should return
    Code:
    a,4
    a,5
    a,8
    b,2
    b,9
    b,8
    b,5
    b,1
    c,3
    c,9
    c,4
    c,5
    SELECT `Item`, `Price` ...
    WHERE `Price` = (SELECT MAX(`Price`) FROM `table1` WHERE `Item` = t1.`Item`)
    should return
    Code:
    a,8
    b,8
    b,8
    c,9
    Its the duplicate (b,8) pair that I want to rid off. Hence the addition of DISTINCT with MAX in where

    SELECT DISTINCT `Item`, `Price` ...
    WHERE `Price` = (SELECT MAX(`Price`) FROM `table1` WHERE `Item` = t1.`Item`)
    should return
    Code:
    a,8
    b,8
    c,9
    MySQL 5.1

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, you are right, you would use DISTINCT this way

    the real problem here is why {b,8} occurs more than once
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Code:
    SELECT `Item`, MAX(Price)
    FROM table1
    GROUP BY Item
    Simple enough ? Think that's what you want!
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  10. #10
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    True, except when an ORDER BY in included, its taking far more time.
    Code:
    SELECT `Item`, MAX(`Price`)
    FROM `table1`
    GROUP BY `Item`
    ORDER BY `Price` DESC
    MySQL 5.1

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you should always only ever ORDER BY one of the columns in the SELECT

    SELECT `Item`, MAX(`Price`) as maxprice
    FROM `table1`
    GROUP BY `Item`
    ORDER BY maxprice DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Sorry - thats what I meant - forgot to add that here.
    Code:
    SELECT `Item`, MAX(`Price`)
    FROM `table1`
    GROUP BY `Item`
    ORDER BY MAX(`Price`) DESC
    MySQL 5.1

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what indexes have you defined?

    please do a SHOW CREATE TABLE for your table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Code:
    CREATE TABLE `test` (
    `ID` bigint(20) NOT NULL auto_increment,
    `ShopID` bigint(20) NOT NULL,
    `Item` varchar(255) NOT NULL,
    `Price` float(5,2) NOT NULL,
    `Position` varchar(10) NOT NULL,
    PRIMARY KEY  (`ID`),
    UNIQUE KEY `ShopID` (`ShopID`,`Item`),
    KEY `Item` (`Item`(100)),
    KEY `Price` (`Price`)
    ENGINE=MyISAM AUTO_INCREMENT=5001 DEFAULT CHARSET=latin1
    It may be worth mentioning that I had a Position = 'some-text' in my WHERE clause of my queries. Just didnt mention it here.
    MySQL 5.1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •