>
> I'm trying to find the highest rated items (max of 10 and based on an
> average of all the times that item was rated). Since many of the top
> rated items will have the same rating, and I need to choose the "top"
> 10, I further want to base it on traffic to that item.
>
> So said another way:
>
> - Calculate average rating for every distinct item, based on
> multiple record entries per item
> - Find the highest rated
> - If the top 10 items have the same average rating, then take
> the traffic each of those items has received and rank based on that
>
> I have two tables that are in question:
>
> Reviews (the ratings table)
> - rating
> - LinkIDFK2
>
> Traffic (the table that hold traffic data for each item)
> - LinkIDFK (same thing as LinkIDFK2 above)
> - Hits (traffic number)
SELECT
AVG(r.rating) AS rating,
r.LinkIDFK2
FROM
Reviews r INNER JOIN Traffic t
ON r.LinkIDFK2 = t.LinkIDFK
GROUP BY
LinkIDFK2
ORDER BY
rating DESC,
t.Hits DESC
Depending on your database you can usually limit the numer of
results by using LIMIT or TOP.
Jochem
--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
