Page 1 of 1

Ordering results from a MySQL query

Posted: Thu Sep 11, 2003 10:29 pm
by SBukoski
Ok, I have the following query I am generating (I will explain everything below):

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.model
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!

Posted: Thu Sep 11, 2003 10:39 pm
by JAM
Use

Code: Select all

... ORDER BY AVG(rt.rating) ...
as opposite to using the alias name of the same...
The database sets the order first to get the right set of results. So it doesn't know what the field alias is just yet.
Hope it was clearly explained enough.

Posted: Thu Sep 11, 2003 11:26 pm
by SBukoski
That was actually one of the first things I tried to get it to work. Unfortunately when I do that it generates the following error:

Invalid use of group function

Any other suggestions?

Posted: Fri Sep 12, 2003 5:48 am
by JAM
REMOVED due to errors. Thanks for mentioning it.

Posted: Fri Sep 12, 2003 11:31 pm
by SBukoski
Ummm.... I will? :oops:

Based on what you said (I believe), I need to include each of my variables I am capturing in the SELECT clause in the ORDER BY clause? This doesn't make a whole lot of sense, but I tried it anyway, and still got the same error as before.

Posted: Sat Sep 13, 2003 3:45 am
by JAM
REMOVED due to errors. Thanks for mentioning it.

Posted: Tue Sep 16, 2003 12:05 pm
by SBukoski
As a follow up, I solved the issue. I did some reading up through the MySQL help files and discovered a function called IFNULL. I read that with NULL columns, they will always be displayed first. So I was able to use the function in my SELECT statement and converted any NULL columns to empty strings, which allowed me to display them properly.

Also, about the GROUP BY. According to the docs, in MySQL you do NOT need all the variables you have selected in the GROUP By clause. just thought I'd mention that as well.

Posted: Tue Sep 16, 2003 6:32 pm
by JAM
Youre right. I've been working very intence with Crystal Reports, VBA, Pascal and MSSql the last couple of weeks were apparently grouping by ALL fields is a must.

I was blinded by that when answering. Glad you made it despite that.