Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nzRLC-00009R-8r for pgsql-sql@arkaria.postgresql.org; Thu, 09 Jun 2022 23:13:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nzRLA-0003Gb-U5 for pgsql-sql@arkaria.postgresql.org; Thu, 09 Jun 2022 23:13:24 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nzRLA-0003GS-Kp for pgsql-sql@lists.postgresql.org; Thu, 09 Jun 2022 23:13:24 +0000 Received: from mail-ed1-x52a.google.com ([2a00:1450:4864:20::52a]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nzRL8-00045p-6W for pgsql-sql@lists.postgresql.org; Thu, 09 Jun 2022 23:13:23 +0000 Received: by mail-ed1-x52a.google.com with SMTP id b8so16404962edj.11 for ; Thu, 09 Jun 2022 16:13:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=sboUNDbBtW9trVXr0FrVPTIoi351x69y+RKn+UOsBXM=; b=RF8siH5nyZoe3jW/LToHNS6C9YvZhbMyhF6RvsNKT7Jz681Lkh3gVtUkVrCLNka24r qXY37wWUm3JmvH06mP4uUfKRht0DdrcEzMkXfOWJYyT+o6wp/KrQnNjQrSS6X/IfySfi xm5UZvyO51RRzR9T8b7sWxzva+xDlPKRgnykOPfQ9fGu9d+j2O8b1Z8zrNvVcOCmKuXj yOw2N9vwkNSzKdBx3j3eTmWD2iJP3wwTWiY6AqmmOPZ0rZwi4s9zSZYvZ4WFQwOlqy2r SKv6gsL8pifYua7gyXy1W+DnKmTNjT3tIV676Mj5jLDqRLiFXxC0ezJm6G4OJme1ZlCc S+7g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=sboUNDbBtW9trVXr0FrVPTIoi351x69y+RKn+UOsBXM=; b=gWZrvnxROAd5yAmtDSa2kSZ+kP6P5z3Hqa8p8hsawSI3pyzpLfL6Ln++J3GQodFimF pOARVze12ob2pkQUm0yWjAoolDhuVN17IkNSNI2JX4KC1ECNyYKF/8QTOsAv5K4vMRpO CsGFXYMV+pkbrBSQGmV/A2ipnv/2z1jWoaTOs3ifbbFK5IoXB4YoDhRuQoKl7hkRNPcO QnO2awm+0dNQ0SCbBL6NT7zfJDdfR/P3+sRHIQMO9SXAMbIGZ1ZTR20zf5rL/Po2f4f5 lS4cAdlzAobCUSwWR2bLXgbCt15kQCCqK/rvhjf5YgZWBPq8BKd5RxMSdmbhCJXhJFX9 rqmg== X-Gm-Message-State: AOAM530URcGYnvVWbqQLsDutVceLpOLUwp7jpo+DUnnYcdjGwTDwIQOH 8kjeXHzoxIIPcDmfNwVwnXkeprT470+Np5TzXOADzy8E X-Google-Smtp-Source: ABdhPJyvWAMqlsYRiY/odpWRUy4ui6WSnFTThvyMYqnsbUq/Lkq+zMAytTlClPRhMd2PGW08Bc6RqIQFwtaLekywgmA= X-Received: by 2002:aa7:d989:0:b0:431:75d5:f204 with SMTP id u9-20020aa7d989000000b0043175d5f204mr21408655eds.230.1654816400809; Thu, 09 Jun 2022 16:13:20 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:ab4:a651:0:0:0:0:0 with HTTP; Thu, 9 Jun 2022 16:13:20 -0700 (PDT) In-Reply-To: References: From: Shaozhong SHI Date: Fri, 10 Jun 2022 00:13:20 +0100 Message-ID: Subject: Re: A function to find errors in groups in a table To: Steve Midgley Cc: pgsql-sql Content-Type: multipart/alternative; boundary="000000000000bd21ea05e10bf751" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000bd21ea05e10bf751 Content-Type: text/plain; charset="UTF-8" The first two can be regarded right. Certainly, the last one must be wrong. How can a primary flow get into a secondary flow? That is odd. Regards, David On Thursday, 9 June 2022, Steve Midgley wrote: > > > On Thu, Jun 9, 2022 at 6:37 AM Shaozhong SHI > wrote: > >> There is a table full of grouped values like the following >> >> nodeid link_type primary >> 11 outflowlink 1 >> 11 inflowlink 1 >> 11 outflowlink 2 >> >> Primary of 1 indicates a primary water course. Primary of 2 indicates a >> secondary water course. >> >> Obviously, one of the out flow links is an error, as its primacy value is >> 2. It is wrong that water flows from a primary water course into a >> secondary water course. >> >> How can a function can be designed to find and report such errors? >> > > Is the problem that there exists this row: > > 11 outflowlink 2 > > But there is no corresponding row: > > 11 inflowlink 2 > > So that you need to find all "orphan" rows that don't have a corresponding > member with the opposing data field inflowlink or outflowlink where the > "primary" column is the join field between the two rows? > > Thanks for clarifying your problem, > Steve > --000000000000bd21ea05e10bf751 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable The first two can be regarded right.=C2=A0 Certainly, the last one must be = wrong.=C2=A0 How can a primary flow get into a secondary flow?

That is odd.

Regards,

<= div>David

On Thursday, 9 June 2022, Steve Midgley <science@misuse.org> wrote:

On Thu, J= un 9, 2022 at 6:37 AM Shaozhong SHI <shishaozhong@gmail.com> wrote:
There is a table ful= l of grouped values like the following

nodeid=C2=A0 =C2=A0 =C2=A0link_type=C2=A0 =C2= =A0 =C2=A0 primary
11=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0outflowlink=C2=A0 =C2=A0 =C2=A0 =C2=A01
11=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 inflowlink=C2=A0 =C2=A0 =C2=A0 1
11= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 outflowlink=C2=A0 =C2=A0 = =C2=A02

Primary of 1 indicates a primary wat= er course.=C2=A0 Primary of 2 indicates a secondary water course.

Obviously, one of the out flow links is an error, as its pr= imacy value is 2.=C2=A0 It is wrong that water flows from a primary water c= ourse into a secondary water course.

How can a fun= ction can be designed to find and report such errors?

Is the problem that there exists this r= ow:

11=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 outflowlink=C2=A0 =C2=A0 =C2=A02
<= div>
But there is no corresponding row:

<= div>11=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 inflowlink=C2=A0 =C2=A0 =C2=A02=C2=A0=C2=A0

So that you need to find all "orphan" rows that don't= have a corresponding member with the opposing data field inflowlink or out= flowlink=C2=A0where the "primary" column is the join field betwee= n the two rows?

Thanks for clarifying your problem= ,
Steve=C2=A0
--000000000000bd21ea05e10bf751--