Re: nextval per counted

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



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

    
          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.
          

    Yeah, in this case I'm
      actually trying to assign an id in place of a null, but only one
      id for all null for one ma.  One thought was to simply use the
      "ma" value but the table constraints are such that that cannot
      be.  

      
      TL/DR

      This is pedigree  ego/ma/pa triplet work.  Set of ego with a
      mother and missing father can be considered a sibship (one
      mother/father pair) or a "half-sibship" where each child get
      assigned a unique father (one
        mother/N fathers).  In that latter case, the sequence
      works perfectly.  We don't allow for mother/mother, the analysis
      tools are not up for that (yet).

      
      Thanks for your time,

      rjs