with
dados_geral as (
        select
            idfilial,
            idfilialsaldo,
            filialfantasia,
            filialsaldofantasia,
            idgrupopreco,
            idcodigonbm,
            idcodigonbm,
            idproduto,
            idgradex,
            gradex,
            idgradey,
            gradey,
            iddepartamento,
            descricao,
            codigofabricante,
            customedio,
            coalesce(idpromocao, 0) as idpromocao,
            precovenda,
            precosistema,
            percentualdesconto,
            idmarca,
            marca,
            lpad(cast(idproduto as varchar),6,'0') || lpad(cast(idgradex as varchar),4,'0') || '01180.jpg' as imagem,
            idmenu,
            idsubmenu,
            idfiltro,
            menu,
            submenu,
            filtro,
            thumbs,
            datahoramovimentothumbs,
            prazofinal,
            bnds as bndes,
            rank() over (partition by idproduto, idgradex order by rank)
        from
            b2b.pwsearch_cache('PR', '', 0,0,0,0,  0,0, 1,2 ,0,75,0, '99')
            where
            1=1
            and idproduto not in (920019)


        -- order by está sendo feito dentro da pwsearch
),
marcas as (
    select '['||string_agg(row_to_json(tb)::varchar,',')||']' as marcas
    from (
      select
          distinct on (1,2)
          idmarca,
          marca
      from dados_geral
    ) tb
),
filiais as (
    select '['||string_agg(row_to_json(tb)::varchar,',')||']' as filiais
    from (
        select
          distinct on (1,2)
          idfilial,
          idfilialsaldo,
          filialfantasia,
          filialsaldofantasia,
          idfilial::varchar||idfilialsaldo::varchar as keyfilial
        from dados_geral
    ) tb
),
menus as (
    select '['||string_agg(row_to_json(tb)::varchar,',')||']' as menus
    from (
        select
          distinct on (1,2)
          idmenu,
          menu
        from dados_geral
    ) tb
),
submenus as (
    select '['||string_agg(row_to_json(tb)::varchar,',')||']' as submenus
    from (
        select
          distinct on (1,2)
          idsubmenu,
          submenu
        from dados_geral
    ) tb
),
dados as (
    select
    distinct on (idproduto, idgradex)
        *
    from dados_geral dg
    join marcas m on true
    join filiais f on true
    join menus me on true
    join submenus sm on true
    where
      1=1
      --and dg.idfilial = 10222

),
count_dados as (

    select
        d.*,
            count (d.*) over () as totalderegistros
        from dados d
)

select
    cd.*
from count_dados cd

order by cd.precovenda asc
 offset 0 LIMIT 16