Message-ID: <49480.192.168.1.108.1190586396.squirrel@msqr.us>
Date: Mon, 24 Sep 2007 10:26:36 +1200 (NZST)
Subject: How pull 
From: "Matt Magoffin" <postgresql.org@msqr.us>
To: pgsql-sql@postgresql.org
User-Agent: SquirrelMail/1.4.8-4.0.1.el4
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Importance: Normal

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@