I do not believe you can do this without a subquery - you are trying
to get 2 separate pieces of information from your data
* some data about the record having MAX(rank) for each category
and
* the count of records in each category
Hi, I guess i try to answer my own question which end up with creating stored procedure.
Unless you have direct query idea.
This function cut the half of query time, as my concern about postgres count agregate function is always slower than I expected.
SQL:
CREATE TYPE product_type as
(sku int4, category int4, display_name varchar(100),rank int4, category_count);
CREATE OR REPLACE FUNCTION get_toprank_product_category (text) returns setof product_type
as '
DECLARE
kwd ALIAS for $1;
mrow RECORD;
retrow prdtcat_searchresult;
tempcount int4;
prevcatnum int4 ;
i int4;
BEGIN
tempcount = 0;
prevcatnum := 0;
I:=0;
FOR tbrow IN
select * from product order by category, rank
LOOP
i := i+1;
IF prevcatnum != mrow.catnum OR i = 1 THEN
prevcatnum := mrow.catnum;
if i > 1 THEN
RETURN NEXT retrow;
END IF;
retrow.catnum := mrow.catnum;
retrow.corenum :=mrow.corenum;
retrow. mernum := mrow.mernum;
retrow.mersku := mrow.mersku;
tempcount = 1;
retrow.catcount := tempcount;
prevcatnum := mrow.catnum;
ELSE
tempcount := tempcount + 1;
retrow.catcount := tempcount;
END IF;
END LOOP;
RETURN NEXT retrow;
RETURN;
END'
language 'PLPGSQL';
