tricky SQL when joining

Question

I've two tables, shows and objects. I want to print out the latest objects, and the shownames for them. Right now I'm doing it this way:

SELECT MAX(objects.id) as max_id, shows.name, shows.id 
FROM shows, objects 
WHERE shows.id = objects.showId
GROUP BY shows.name 

however, if I also want to fetch the episode of the object I can't put it like SELECT object.episode [...], because then wont automatically select the object which is MAX(objects.id), so my question is how to do that?

If you haven't already figured out my tables they're like this:

• Shows
  • id
  • name

and also:

• Objects
  • id
  • name
  • episode
  • season
  • showId

Using MySQL. Thanks!

Answer 1

SELECT objects.id as max_id, shows.name, shows.id 
FROM shows, objects 
WHERE shows.id = objects.showId
ORDER BY objects.id DESC
GROUP BY shows.name 
LIMIT 1

Does that do what you need?

Answer 2

Something like this (untested):

SELECT objects.id as max_id, objects.episode, shows.name, shows.id
  FROM shows, objects 
 WHERE shows.id = objects.showId
   AND objects.id = (
        SELECT MAX(id) FROM objects
         WHERE name = shows.name
       )

Answer 3

I get the feeling something like this would work also:

SELECT objects.id, objects.episode, shows.name, shows.id 
FROM shows 
JOIN objects AS objects_for_max ON shows.id = objects.showId
JOIN objects ON objects.id=MAX(objects_for_max.id)
GROUP BY shows.name