Re: nextval per counted

"David G. Johnston" <david.g.johnston@xxxxxxxxx> · Fri, 27 Jan 2023 14:31:14 -0700

On Fri, Jan 27, 2023 at 2:25 PM Rob Sargent <robjsargent@xxxxxxxxx> wrote:

    On 1/27/23 14:20, David G. Johnston
      wrote:

          On Fri, Jan
              27, 2023 at 1:59 PM Rob Sargent <robjsargent@xxxxxxxxx>
              wrote:

             I'm
                trying to craft SQL to invoke a sequence nextval once
                per grouped value.

            This seems
              like a very unusual usage of nextval/sequences...

          with cleanup
            as (
            select
            DISTINCT e.ma,
            coalesce(e.pa,
            'fix') as pa from ...
          ), compute as
            (
            select ma,
            pa, nextval(...) from cleanup
          )
          select * from
            compute ... -- do whatever else you want

             So far I
                have this:

              with
                  husb as(

                select e.ma, count(distinct
                  coalesce(e.pa,
                  nextval('egogen')::text)) as mates 

                from
                  emp_all_by3 e group by e.ma
                  order by mates

                ) 

                select
                  mates, count(*) 

                  from husb 

                  group by mates order by mates desc;

            Your "order
              by mates" in the CTE is totally pointless and wasting
              resources.

          David J.

    Yeah, it wasn't when that was the last bit executed...

    Seems asking a sequence for an id isn't too unusual?  Or are they
    specifically intended for primary keys?

Yes, their design is excellent for surrogate primary keys.  I don't even know what to call what you are doing but it isn't that.  It seems like some form of counting but stuff like "row_number" and "count" perform that function.  I don't think I'd trust using them as a counter...for that I'd do counting in the query then have a separate aspect, in user-space, not the system catalogs, that increments a counter.

David J.