Re: hpw to Count without group by

>
> 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';