ORDER BY Not Working, Better to use Join?

Here is my mysql query:

SELECT DISTINCT films.film_id,films.film_title, films.studio, films.release, films.image, films.poster_primary, films.imdb, trailers.trailer_id, trailers.trailer_title, trailers.vimeo_files, trailers.runtime, trailers.description
 FROM films,trailers 
WHERE STR_TO_DATE(films.release, '%M %d,%Y') BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 3 WEEK) 
AND DATE_ADD(CURRENT_DATE(), INTERVAL 14 MONTH) 
AND films.film_id = trailers.film_id 
AND trailers.trailer_title LIKE 'TV Spot %'
					GROUP BY films.film_id
					ORDER BY films.views DESC,trailers.trailer_id DESC LIMIT 8");

What I’m attempting is to grab the most recent TV spot for the top 8 films. But, it refuses to allow the ordering of trailers.trailer_id DESC, and shows the first TV spot instead (lower ID number).

How can I rectify this and should I be using a JOIN here?

Cheers!
Ryan

it doesn’t “refuse”

also, you actually are already doing a join

your problem is the GROUP BY – you’re only going to get one trailer per film this way, so there’s no use trying to sort the trailers within a film, because there is only one

and which trailer are you getting per film? it’s indeterminate

if you want the latest trailer for each film, you’ll need to ditch the GROUP BY and add a join to a MAX subquery

1 Like

Sweet, I think I got it.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.