Hello,

I am a SQL beginner and dont know how to write an advanced select statement.

I have two tables, 'audiobooks' with column 'ID' (plus others) and 'ratings' with columns 'audiobook_ID', 'overall_rating', 'production_rating' and 'book_rating'.

I want to sort my audio books by the average 'overall_rating' of the corresponding ratings.

And if it it possible, if two 'overall_rating' averages are equal, would then use 'production_rating' and then 'book_rating' averages.

Thanks a lot

i generally know what you need to do but without the tables to practice with I couldnt tell you definatly.

you need to group your items in the 2nd table by avg and just have that in your order by list in the order you said.

you would need to only collect the id and not even join the tables i think for a much faster run.
select audiobook_ID, avg(overall_rating) as avg_overall_rating, avg(production_rating) as avg_pro_rating, AVG(book_rating) as avg_book_rating from ratings
group by audiobook_ID
order by avg(overall_rating), avg(production_rating), AVG(book_rating)

but you could probably get all the info from the other table if you joined them and grouped by each item in that other table. but this way would be fastest i think (perhaps not thou) I would like to know which way would be faster.

ideally you could write it in a stored procedure and build a temp table and fill it with the first info gathered in the single query and then updated with the other info in a looping single query and then sent back to the page as a single recordset

Lorddog

hello lorddog, thanks for the answer, sorry for the late response, havent been here a while.

It works but only if I use the "as" variables after the ORDER BY, it doesnt seem to like the AVG statement there

I then joined the two tables, found it more elegant that way and I am not that speed dependant.

If speed ever becomes a problem I will try the other method.

Edit: On my database there is nearly no difference between the simple and the joined select, so the added selects for the single books would take much more time.