Ordering results from a MySQL query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
SBukoski
Forum Contributor
Posts: 128
Joined: Wed May 21, 2003 10:39 pm
Location: Worcester, MA

Ordering results from a MySQL query

Post 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!
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
SBukoski
Forum Contributor
Posts: 128
Joined: Wed May 21, 2003 10:39 pm
Location: Worcester, MA

Post 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?
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

REMOVED due to errors. Thanks for mentioning it.
Last edited by JAM on Tue Sep 16, 2003 6:26 pm, edited 2 times in total.
SBukoski
Forum Contributor
Posts: 128
Joined: Wed May 21, 2003 10:39 pm
Location: Worcester, MA

Post 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.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

REMOVED due to errors. Thanks for mentioning it.
Last edited by JAM on Tue Sep 16, 2003 6:26 pm, edited 1 time in total.
SBukoski
Forum Contributor
Posts: 128
Joined: Wed May 21, 2003 10:39 pm
Location: Worcester, MA

Post 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.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
Post Reply