Thread: nextval per counted
I'm trying to craft SQL to invoke a sequence nextval once per grouped value.
So far I have this:
The following lets me count the "fixes" as a mate
Any pointers appreciated.
So far I have this:
with husb as(which works nicely but it "ids" each null separately.
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;
The following lets me count the "fixes" as a mate
with husb as(but I would love to able to assign a single "nextval" to those fixes.
select e.ma, count(distinct coalesce(e.pa, nextval('egogen')::text)) mates
from emp_all_by3 e
where ma is not null
group by e.ma order by mates
)
select mates, count(*) from husb group by mates order by mates desc;
with husb as(
select e.ma, coalesce(e.pa,'fix') as pa
from emp_all_by3 e
where e.ma is not null
),
fixed as (
select e.ma, count(distinct e.pa) mates
from husb e group by e.ma order by mates
)
select mates, count(*) from fixed group by mates order by mates desc;
Any pointers appreciated.
On Fri, Jan 27, 2023 at 1:59 PM Rob Sargent <robjsargent@gmail.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 (
), compute as (
select ma, pa, nextval(...) from cleanup
)
select * from compute ... -- do whatever else you want
Your "order by mates" in the CTE is totally pointless and wasting resources.
David J.
On 1/27/23 14:20, David G. Johnston wrote:
Yeah, it wasn't when that was the last bit executed...On Fri, Jan 27, 2023 at 1:59 PM Rob Sargent <robjsargent@gmail.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 (), compute as (select ma, pa, nextval(...) from cleanup)select * from compute ... -- do whatever else you wantYour "order by mates" in the CTE is totally pointless and wasting resources.David J.
Seems asking a sequence for an id isn't too unusual? Or are they specifically intended for primary keys?
On Fri, Jan 27, 2023 at 2:25 PM Rob Sargent <robjsargent@gmail.com> wrote:
On 1/27/23 14:20, David G. Johnston wrote:Yeah, it wasn't when that was the last bit executed...On Fri, Jan 27, 2023 at 1:59 PM Rob Sargent <robjsargent@gmail.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 (), compute as (select ma, pa, nextval(...) from cleanup)select * from compute ... -- do whatever else you wantYour "order by mates" in the CTE is totally pointless and wasting resources.David J.
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.
On 1/27/23 14:31, David G. Johnston wrote:
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.On Fri, Jan 27, 2023 at 2:25 PM Rob Sargent <robjsargent@gmail.com> wrote:On 1/27/23 14:20, David G. Johnston wrote:Yeah, it wasn't when that was the last bit executed...On Fri, Jan 27, 2023 at 1:59 PM Rob Sargent <robjsargent@gmail.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 (), compute as (select ma, pa, nextval(...) from cleanup)select * from compute ... -- do whatever else you wantYour "order by mates" in the CTE is totally pointless and wasting resources.David J.
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.
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