SELECT id,food_id,title,MAX(score)
FROM ubi_ingredient
GROUP BY food_id

but if there is a ingredientwith the same score I want the one withthe max score and I don' find the way ^^

Can you help me, please ?Thanks in advance.

r937
—
2013-05-22T16:31:09Z —
#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

whisher
—
2013-05-22T18:50:46Z —
#3

r937 said:

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)

r937 said:

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 ?

whisher
—
2013-05-24T21:16:22Z —
#4

bumpish ^^

r937
—
2013-05-24T22:12:57Z —
#5

whisher said:

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

whisher
—
2013-05-25T18:00:51Z —
#6

r937 said:

for more help on that, google "groupwise max" on the mysql.com site

thanks for the hint.

this seems to work

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