CODEWITHSUNDEEP
mysqlgroup-bymax
xylar
xylar

Reputation: 7673

Get a field from a table when using MAX function

I have the following MySQL statement which is working well.

SELECT c.id, IFNULL(MAX(l.id), 0) as level_id
FROM course c
LEFT JOIN level l ON c.level_id = l.id
WHERE c.user_id = '123'    
GROUP BY c.id

A course can have multiple levels so the IFNULL(MAX(l.id), 0) works by selecting the highest.

However, instead of returning the id of the highest level, I would like to get the name. I tried the following but MySQL doesn't like joining using a value in the select.

SELECT c.id, IFNULL(MAX(l.id), 0) as level_id, l2.name
FROM course c
LEFT JOIN level l ON c.level_id = l.id 
INNER JOIN level l2 ON level_id = l2.id
WHERE c.user_id = '123'     
GROUP BY c.id

I have simplified my statements in my questions (for readability), so hopefully the problem still makes sense.

Upvotes: 0

Views: 31

Answers (2)

Kickstart
Kickstart

Reputation: 21533

An alternative is to do a sub query and join the results of that back against the level table to get the level name:-

SELECT sub0.id, sub0.level_id, l2.name
FROM
(
    SELECT c.id, IFNULL(MAX(l.id), 0) as level_id
    FROM course c
    LEFT JOIN level l ON c.level_id = l.id 
    WHERE c.user_id = '123'
    GROUP BY c.id
)
LEFT OUTER JOIN level l2 ON l2.id = sub0.level_id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

With this type of query, I think the easiest method is the substring_index()/group_concat() trick:

SELECT c.id, COALESCE(MAX(l.id), 0) as level_id,
       SUBSTRING_INDEX(GROUP_CONCAT(l2.name ORDER BY l.id DESC), ',', 1) as name
FROM course c LEFT JOIN
     level l
     ON c.level_id = l.id INNER JOIN
     level l2
     ON level_id = l2.id
WHERE c.user_id = '123'
GROUP BY c.id;

Note that the WHERE is misplaced in your query.

Caveats on this approach:

  • You need a different separator if name might have commas.
  • There is an internal limit on the length of the group_concat() intermediate result. If this is an issue, the length can be increased.

Upvotes: 1

Related Questions