samedi 25 avril 2015

Multiple ORDER BY (get latest element) MySQL


I have the following tables : Radios, Podcasts and Shows. A radio has many podcasts, and a podcasts has many shows. Each podcast can be ordered by its history, and each show can be ordered by its publication_date.

I want to get all the podcasts associated with their latest show.

The query looks like that :

SELECT r.name AS radio_name,Pod.*,Sh.* 
FROM podcasts Pod 
  INNER JOIN radios r ON (r.id=Pod.radio_id) 
  INNER JOIN shows Sh ON (Sh.podcast_id=Pod.id) 
ORDER BY Pod.history LIMIT 5

I'd like to have a second ORDER BY Sh.publication_date but I don't really know where it should be.


Aucun commentaire:

Enregistrer un commentaire