How pull - Mailing list pgsql-sql

From Matt Magoffin
Subject How pull
Date
Msg-id 49480.192.168.1.108.1190586396.squirrel@msqr.us
Whole thread Raw
Responses Re: How pull
List pgsql-sql
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@


pgsql-sql by date:

Previous
From: John Mulkerin
Date:
Subject: Re: Intermittent Empty return
Next
From: "Filip Rembiałkowski"
Date:
Subject: Re: How pull