[RESOLVED] Sort results by values in another table

[DigiRev]

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.

[si_the_geek]

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

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

[DigiRev]

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.

[si_the_geek]

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

If so, these additions should do it:

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

[DigiRev]

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

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.

[DigiRev]

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

Sorry for the stupid question.

[si_the_geek]

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:

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!