Re: nextval per counted

On Fri, Jan 27, 2023 at 1:59 PM Rob Sargent <robjsargent(at)gmail(dot)com> 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.