Re: hpw to Count without group by

Поиск
Список
Период
Сортировка
От Ragnar Hafstað
Тема Re: hpw to Count without group by
Дата
Msg-id 1117666370.31064.5.camel@localhost.localdomain
обсуждение исходный текст
Ответ на hpw to Count without group by  (Yudie Pg <yudiepg@gmail.com>)
Список pgsql-general
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



В списке pgsql-general по дате отправления:

Предыдущее
От: Simon Riggs
Дата:
Сообщение: Re: PITR restore hot standby
Следующее
От: Havasvölgyi Ottó
Дата:
Сообщение: Re: interval integer comparison