more of a MySQL question....

..sorting on the biggest result of a division of the column values

[dmmh]

in order to be able to use proper sorting functionality on my site I need my query to be modified to calculate the division of two INT values in a returned row

I need this for rating functionality, so if I would simply ORDER BY (field_rating), this wouldnt be precise enough, as it may have a value of 10 with 10 votes, which averages 1 and another row might be 9, but with 1 votes, which would mean the first scenario would be shown first, but it should be the other way around...if you know what I mean

my query now:

$query = 'SELECT * FROM comments'.
" INNER JOIN users_table".
" ON (users_table.userid = comments.added_by)".
" WHERE comments.mid = '$mid'".
" ORDER BY comments.$sort $order".
" LIMIT $from, $max_results";

[arran]

dmmh,

I've created an small example doing what I think you're asking (see below). I did it in Sybase but you can tweak the SQL to run on mySQL.

fsql-1) create table comments ( product_id int, rating int);
fsql-1) create table products (product_id int, product_name varchar(10));
fsql-1) insert into products values (0, "product0");
fsql-1) insert into products values (1, "product1");
fsql-1) insert into comments values (0, 4);
fsql-1) insert into comments values (0, 9);
fsql-1) insert into comments values (0, 7);
fsql-1) insert into comments values (1, 3);
fsql-1) insert into comments values (1, 10);
fsql-1) insert into comments values (1, 9);
fsql-1) select p.product_name, sum(c.rating)/count(c.rating) as 'av_score' from products p, comments c where p.product_id = c.product_id group by p.product_name order by av_score asc;
product_name av_score
------------ -----------
product0 6
product1 7

(2 rows affected)

You probably want to use floats instead of ints for the rating (my averages are truncated). Either that or use a convert statment in the select statement.

[dmmh]

thank you for the effort, but thats not quite what I meant :)

I have one column which holds ratings, which is just an integer. If a user votes, first I query for the old value then add the new one and write it to thr DB.
So if there were ten '10' votes, this value would be 100 for a certain row.

Example:
If I would simply order by the biggest value in this column, the results wouldnt be good, as a value of 18 with 2 '9' votes would precede a value of 9 with one vote.

So I need to divide the value of the field containing the big integer by the number of votes, right inside the query, then get the highest out of the calcualtion

think you could tackle it for me? thanks anyways :)

[arran]

So rather than storing a row for each rating you have two columns in the products table, 'total rating' and 'number of reviews'? In that case the select statement would be:

select p.product_name, p.total_rating/p.num_reviews as 'av_score' from products p group by p.product_name order by av_score desc

[dmmh]

mmm, ill give it a go, thought it was more complicated then this