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

Databases
1

Hi,


CREATE TABLE IF NOT EXISTS ingredient(
    id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    food_id TINYINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT 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


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

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

3

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)

so what’s the right way ?

4

bumpish ^^

5

hang on - ish

:slight_smile:

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

6

thanks for the hint.

this seems to work :slight_smile:


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)


SELECT o1.id, o1.title, o1.food_id, o1.score, o1.point
FROM ubi_ingredient o1
WHERE o1.score = ( 
SELECT MAX( score ) 
FROM ubi_ingredient o2
WHERE o1.food_id = o2.food_id ) 
ORDER BY o1.point DESC

2
(the same of 1)


SELECT o1.id, o1.title, o1.food_id, o1.score, o1.point
FROM ubi_ingredient AS o1
INNER JOIN (
        SELECT  food_id, MAX(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

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

8 

SELECT o1.id, o1.title, o1.food_id, o1.score, o1.point
FROM ubi_ingredient AS o1
INNER JOIN (
        SELECT  food_id, MAX(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

:slight_smile:

9

looks good, but you gots an extra “BY” in the last line