SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    fetch the max and than if there is equal take max field

    Hi,
    PHP Code:
    CREATE TABLE IF NOT EXISTS ingredient(
        
    id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
        
    food_id TINYINT UNSIGNED NOT NULL,
        
    title VARCHAR(255NOT NULL,
        
    point TINYINT UNSIGNED NOT NULL,
        
    score INT UNSIGNED DEFAULT 0,
        
    PRIMARY KEY (id),
        
    UNIQUE KEY unique_ingredient_title (title)
    ENGINE=InnoDB DEFAULT CHARSET=utf8
    so with this query I got the ingredient with the
    max score

    PHP Code:
    SELECT id,food_id,title,MAX(score)
    FROM ubi_ingredient 
    GROUP BY food_id 
    but if there is a ingredient
    with the same score I want the one with
    the max score and I don' find the way ^^

    Can you help me, please ?
    Thanks in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    if two ingredients have the same max score, which one do you want?

    by the way, your GROUP BY query is broken, the values that will be returned for id and title will be indeterminate and could come from any of the rows that have the max score
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    if two ingredients have the same max score, which one do you want?
    my goal is to fetch the ingredient with the max score in the category(food) and if there
    two ingredients in same category with the same score fetch the one with the max point
    (each ingredient have a unique value from 1 to n)

    Quote Originally Posted by r937 View Post

    by the way, your GROUP BY query is broken, the values that will be returned for id and title will be indeterminate and could come from any of the rows that have the max score

    so what's the right way ?

  4. #4
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    bumpish ^^

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by whisher View Post
    bumpish ^^
    hang on - ish



    at worst, you could return all of the rows that have the same max score, and use php (or whatever) to pick the one with the highest points

    assuming you fix the GROUP BY error, of course...

    for more help on that, google "groupwise max" on the mysql.com site
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    for more help on that, google "groupwise max" on the mysql.com site
    thanks for the hint.

    this seems to work
    PHP Code:
    SELECT  id,food_id,title,score,point
    FROM 
    (SELECT id,food_id,title,score,point
          FROM ubi_ingredient
          ORDER BY score DESC
    ,point DESC) AS h
    GROUP BY food_id 
    the other two attempts

    1
    (It gets all the rows with the max score)
    PHP Code:
    SELECT o1.ido1.titleo1.food_ido1.scoreo1.point
    FROM ubi_ingredient o1
    WHERE o1
    .score = ( 
    SELECT MAXscore 
    FROM ubi_ingredient o2
    WHERE o1
    .food_id o2.food_id 
    ORDER BY o1.point DESC 
    2
    (the same of 1)
    PHP Code:
    SELECT o1.ido1.titleo1.food_ido1.scoreo1.point
    FROM ubi_ingredient 
    AS o1
    INNER JOIN 
    (
            
    SELECT  food_idMAX(score) AS score
            FROM    ubi_ingredient d
            GROUP BY
                    food_id 
            ORDER BY point DESC      
            
    o2
    ON      o1
    .food_id o2.food_id
    WHERE o1
    .score o2.score 
    ORDER BY o1
    .food_id ASC 

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by whisher View Post
    this seems to work
    the operative word being "seems"

    your query number 1 is fine, but might run slowly, and of course you're returning all the foods in mixed up sequence

    your query number 2 is fine if you remove the ORDER BY clause in the subquery, and add points DESC to the ORDER BY
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    SELECT o1.ido1.titleo1.food_ido1.scoreo1.point
    FROM ubi_ingredient 
    AS o1
    INNER JOIN 
    (
            
    SELECT  food_idMAX(score) AS score
            FROM    ubi_ingredient d
            GROUP BY
                    food_id 
              
    o2
    ON      o1
    .food_id o2.food_id
    WHERE o1
    .score o2.score 
    ORDER BY o1
    .food_id ASC,BY o1.point DESC 

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    looks good, but you gots an extra "BY" in the last line
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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