mysql. sort on temporary column based

I have 2 tables called albums and photos with ratings being one of the attribute in the photos table.

I want to know whether it is possible to sort albums based on a total number of photos in an album. The total number of photos should be generated based on a condition on the ratings attribute.

I dont want to add a new column to the albums table having the ph开发者_如何学JAVAotos counts based on rating as the ratings keep changing.

Instead of creating new column, you can achieve the same by using inner query (will act like temp table)

Like...

SELECT * FROM album a
JOIN ( SELECT yourlogic(rating) AS total_no_photos,  album_id FROM photo
      WHERE yourlogic  
      GROUP BY album_id 
       ) temp_photo
ON temp_photo.album_id = a.album_id
ORDER BY total_no_photos

1. The total number of photos in an album should be fetched in a subquery.
2. Give a meaningful alias to it say photos_count
3. You can use the photos_count in order by clause. The result will be sorted.