Hello,
My SQL skills are limited and I'm struggling with a query where I want to
return a single item of an aggregate join. The query looks like this:
select (case when agg.avg_rating is null then 0.0 when agg.avg_rating < 0.75 then 0.5 when agg.avg_rating < 1.25
then1.0 when agg.avg_rating < 1.75 then 1.5 when agg.avg_rating < 2.25 then 2.0 when agg.avg_rating < 2.75 then
2.5 when agg.avg_rating < 3.25 then 3.0 when agg.avg_rating < 3.75 then 3.5 when agg.avg_rating < 4.25 then 4.0
whenagg.avg_rating < 4.75 then 4.5 else 5.0 end) as avg_rating, count(item.itemid) as item_count
from media_item item
inner join ( select rating.mediaitem_userrating_hjid as ritemid, avg(rating.rating) as avg_rating from
media_item_ratingrating, media_item item where rating.mediaitem_userrating_hjid = item.itemid group by
rating.mediaitem_userrating_hjid
) as agg
on item.itemid = agg.ritemid
group by avg_rating
order by avg_rating desc
and a sample of results is this:
avg_rating | item_count
------------+------------ 5.0 | 21 4.0 | 33 3.0 | 13 2.0 | 4
1.0 | 1
What I want as well is the ID of the item (and possibly it's avg_rating
value) from the "agg" join with the highest avg_rating for each output
row... something like this
avg_rating | item_count | item_id | item_rating
------------+----------------------------------- 5.0 | 21 | 109890 | 4.9 4.0 | 33 | 89201
| 4.1 3.0 | 13 | 119029 | 2.8 2.0 | 4 | 182999 | 2.2 1.0 | 1 | 1929 |
1.0
So the intention in this example is that item #109890 has an average
rating of 4.9 and that is the highest rating within the > 4.75 rating
group.
If anyone had any tips I'd greatly appreciate it.
-- m@
