From: Matt Magoffin <postgresql.org@msqr.us>
To: pgsql-sql@postgresql.org
Subject: How pull
Date: Mon, 24 Sep 2007 10:26:36 +1200 (NZST)
Message-ID: <49480.192.168.1.108.1190586396.squirrel@msqr.us> (raw)
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 then 1.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
when agg.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_rating rating, 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@
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql.org@msqr.us
Subject: Re: How pull
In-Reply-To: <49480.192.168.1.108.1190586396.squirrel@msqr.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox