Re: hpw to Count without group by

On Wed, 2005-06-01 at 16:16 -0500, Yudie Pg wrote:
> Hello,
> I have a table, structure like this:

 [...]

> Expected query result:
>
> sku, category, display_name, category_count
> ====================================
> 10001, 5, postgresql, 3
> 10006, 7, photoshop, 2
> 10008, 9, Windows XP, 2
>
> The idea is getting getting highest ranking each product category and
> COUNT how many products in the category with SINGLE query.
>
> the first 3 columns can be done with select distinct on (category) ...
> order by category, rank desc but it still missing the category_count.
> I wish no subquery needed for having simplest query plan.

how about a simple join ?

select sku,category,display_name,count
    from
         (select distinct on (category) category, sku,display_name
                from product order by category,rank
         ) as foo
    natural join
          (select category,count(*) as count
                from product group by category
          ) as bar;

gnari