MySQL join on max value

Question

From joining the tables below on the entry.id, I want to extract the rows from the food_brands table which have the highest type_id - so I should be getting the top 3 rows below, with type_id 11940

food_brands

        id      brand       type_id 
        15375   cesar       11940
        15374   brunos      11940
        15373   butchers    11940
        15372   bakers      11939
        15371   asda        11939
        15370   aldi        11939

types

        id      type      quantity      food_id 
        11940   comm      53453         10497
        11939   comm      999           10496

foods

        id      frequency   entry_id 
        10497   twice       12230
        10496   twice       12230
        10495   once        12230

entries

         id     number  
         12230  26

My attempt at the query isn't filtering out the lower type.id records - so from the table records below in food_brands, i'm getting those with type_id 11940 and 11939. Grateful for any help fix this!

SELECT fb.*
                        FROM food_brands fb
                        INNER JOIN types t ON fb.type_id = t.id
                        INNER JOIN
                        (
                            SELECT MAX(id) AS MaxID
                            FROM types
                            GROUP BY id
                        ) t2 ON t.food_id = t2.food_id AND t.id = t2.MaxID
                        INNER JOIN foods f ON t.food_id = f.id
                        INNER JOIN entries e ON f.entry_id = e.id
                        WHERE entries.id = 12230

didierc - well spotted! updated – bsod99 Mar 28 '13 at 10:21 — bsod99, Mar 28 '13 at 10:21

score 11 · Answer 1 · answered Mar 27 '13 at 19:00

11

A simple subquery should do it just fine;

SELECT * FROM food_brands WHERE type_id=
  (SELECT MAX(t.id) tid FROM types t
   JOIN foods f ON f.id=t.food_id AND f.entry_id=12230)

An SQLfiddle to test with.

answered Mar 27 '13 at 19:00

Joachim Isaksson

176,943
25
281
294

score 4 · Answer 2 · answered Mar 27 '13 at 18:23

4

If you just want to return the rows from food_brands with the max type id, you should be able to use:

SELECT fb.*
FROM food_brands fb
INNER JOIN
(
  select max(id) id
  from types
) t
  on fb.type_id = t.id

See SQL Fiddle with Demo

answered Mar 27 '13 at 18:23

Taryn

242,637
56
362
405

score 0 · Answer 3 · edited Jun 13 '22 at 11:47

I don't know why you are doing all these inner joins after the one on the t2 subquery, since you are only retrieving the columns of fb, but I suppose that you are not showing the whole query, and you just want to get that one fixed.

The issue is actually in the subquery t2: there, for some untold reason, you choose to do a GROUP BY id which changes the MAX function semantic to generate a maximum value per id, and since you are asking the maximum on that very column, MAX and GROUP BY cancel out each other. Just removing the GROUP BY clause fixes the query.

If for some untold reason you cannot remove that clause, perhaps replacing MAX(id) by id and adding ORDER BY id DESC LIMIT 1 would do.

Also, your subquery should probably select also food_id since it is used in the subsequent INNER JOIN clause.

MySQL join on max value

3 Answers3

Linked