Ordering results from a MySQL query
Posted: Thu Sep 11, 2003 10:29 pm
Ok, I have the following query I am generating (I will explain everything below):
To start, I have two tables. One stores a design with all the specifications for that design. Users have the ability to write reviews and rate each design. Those reviews are stored in another table. So there is one row per design in one table, but multiple rows (potentially) for each design in the other.
The intent of the SQL above is to GROUP by the ID (key field) and get an average rating. Getting the average rating works great. However, when I try to sort by the average, it always shows the designs with NO ratings first, and the ones that were reviewed last. For example, with the SQL above, it displays all the designs that have an avg_rating field of null. Then it will cycle through starting with 5.00 down to 0.00. If I change the order type to ASC, it STILL shows the null ones first followed by the 0.00 up to 5.00.
My question is, how do I get it so it is truly sorting by the rating first. I want to see the 5.00 ratings before I see the null ratings.
Any help would be appreciated!
Code: Select all
SELECT t.id, t.name, t.model, t.tons, t.tech, t.era, t.tro, t.new, AVG(rt.rating) AS avg_rating
FROM mbc_tro t
LEFT JOIN review_tro rt ON t.id = rt.mechid AND rt.status = 1
GROUP BY t.id
ORDER BY avg_rating DESC, t.name, t.modelThe intent of the SQL above is to GROUP by the ID (key field) and get an average rating. Getting the average rating works great. However, when I try to sort by the average, it always shows the designs with NO ratings first, and the ones that were reviewed last. For example, with the SQL above, it displays all the designs that have an avg_rating field of null. Then it will cycle through starting with 5.00 down to 0.00. If I change the order type to ASC, it STILL shows the null ones first followed by the 0.00 up to 5.00.
My question is, how do I get it so it is truly sorting by the rating first. I want to see the 5.00 ratings before I see the null ratings.
Any help would be appreciated!