[RESOLVED] Sort results by values in another table

I'm trying to order the results by values in another table.

There is one table named listings with stuff like:

id
name
description

And there are votes users leave on each listing stored in a table votes:

id
listing_id
rating (1-5)

Now, on the main page that displays the listings, there is a filter option for sorting the results by user rating. How would I do this? Can someone give me an example? It's kind of urgent...

Edit: Is this the job for a stored procedure? I don't think he has a MySQL version that supports these though and I've only read briefly on them. If there's another solution, even if it's kind of a dirty fix, that would be better.

Perhaps something like this (not sure if Average is what you want):

Code:

---

SELECT L.id, L.name, L.description, Average(Rating) as AvgRating
FROM Listings L
LEFT JOIN Votes V ON (V.listing_id = L.id)
GROUP BY L.id, L.name, L.description
ORDER BY AvgRating Desc

---

Thanks, si. I used AVG in place of Average since it said it is an "unknown function".

The problem now is, it is showing them like this:

(example):
Listing 1 - 10.0 (3 votes)
Listing 2 - 10.0 (6 votes)
Listing 3 - 10.0 (6888 votes)

Where the ones with the most votes are being displayed after the ones with the fewest.

Any ideas?

Also, the ratings are actually stored from 1 to 3. (1 being bad, 3 being best). I am converting them to a 1 to 10 scale when displaying, not sure if this is relevant.

It sounds like you want the highest ratings first, and within each rating the highest quantity first.

If so, these additions should do it:

Code:

---

SELECT L.id, L.name, L.description, Avg(Rating) as AvgRating, Count(Rating) as NumRating
ORDER BY AvgRating Desc, NumRating Desc

---

Perfect. :D My knowledge of SQL doesn't go far beyond the basics...:o

I'm marking this resolved, but another quick question if you don't mind...

Normally, when I order results by multiple fields, like...first order by name, then if they have the same name, order by date, etc., I would do:

ORDER BY (field1/field2)

I tried modifying this one like that but it didn't work. :( Not sure how to do it.

Anyway, thanks again.

Nevermind, I got it. Just had to separate them with commas and prefix with L.

Sorry for the stupid question.

You just put the list of fields (along with Asc/Desc if wanted [using neither is the same as Asc]), separated by commas.

The first field specifies what to sort on first, and the others are only used if all previous fields are equal.

For the name/date sort, it would be like this:

Code:

---

Order By NameField, DateField, ...

---

..along with Asc/Desc after the field names if apt.


edit: note to self, don't go off to make a coffee while replying to a thread!