Re: nextval per counted

Rob Sargent <robjsargent@xxxxxxxxx> · Fri, 27 Jan 2023 14:25:32 -0700



    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?