Hello,
I have a table, structure like this:
create table product(
sku, int4 not null,
category int4 null,
display_name varchar(100) null,
rank int4 null
)
let say example data:
sku, category, display_name
=======================
10001, 5, postgresql, 132
10002, 5, mysql, 243
10003, 5, oracle, 323
10006, 7, photoshop, 53
10007, 7, flash mx, 88
10008, 9, Windows XP, 44
10008, 9, Linux, 74
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.
Thank you.
Yudie G.
