Re: hpw to Count without group by

[Yudie Pg <yudiepg@xxxxxxxxx>]

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