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 1omzpU-0003hP-11 for pgsql-sql@arkaria.postgresql.org; Mon, 24 Oct 2022 15:57:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1omzpR-0008QK-Cn for pgsql-sql@arkaria.postgresql.org; Mon, 24 Oct 2022 15:57:29 +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 1omzpQ-0008QA-Ty for pgsql-sql@lists.postgresql.org; Mon, 24 Oct 2022 15:57:29 +0000 Received: from premium22-1.web-hosting.com ([68.65.122.103]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1omzpK-0003Ye-ST for pgsql-sql@lists.postgresql.org; Mon, 24 Oct 2022 15:57:27 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=misuse.org; s=default; h=Content-Type:Cc:To:Subject:Message-ID:Date:From:In-Reply-To: References:MIME-Version:Sender:Reply-To:Content-Transfer-Encoding:Content-ID: Content-Description:Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc :Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=C7lJpJ8gGIKp5WTB1C1g8G3jqmSpjgB+d7nShZ80V4k=; b=wxMC5PS4lwPVk0N1tJyA0Eb84T 0Gk+dhs2xth06QIDkDjd7vDp6ZQ8tyAaAyUCn8biicXzyjPXoQJn+7RDWTwhh/NXvkydjGMAkkAKi UiNOihufm9Vthh21EQdIBa7lSUdc9yoRHJUtnvXqyiSPFwZ/ef2LdzcLmJnKGk6h9AYN+0GsNWKZI 69kmRusOtvp7DU0RYI8nYz805Qx1V5YYaY2KHhHvR6nsZXUu1G2huOF94LM3yqwcHIJnzP7S7BesZ UJIypvQcLlR493KdWmPTedo1Wp6cs5QtD3qewfHUClLqX/ARxePVvhkwfr7qJqoBxPtasc0xq71fh hC23OcEQ==; Received: from mail-yw1-f175.google.com ([209.85.128.175]:33656) by premium22.web-hosting.com with esmtpsa (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.95) (envelope-from ) id 1omzpA-002qFk-0t for pgsql-sql@lists.postgresql.org; Mon, 24 Oct 2022 11:57:20 -0400 Received: by mail-yw1-f175.google.com with SMTP id 00721157ae682-36847dfc5ccso90133697b3.0 for ; Mon, 24 Oct 2022 08:57:11 -0700 (PDT) X-Gm-Message-State: ACrzQf389sPnCTKAVpgNh5jkRsLgme8ARmvUQpdyc0bmz+UkyiAjTZek VLxGF+hinR15n9epzIXuUHSRfp12+VL2SBoiIL0= X-Google-Smtp-Source: AMsMyM75vX2CbrT4A125TlYCKWy9s5WI1/HvkFHPuNJIej237CxWIXFI6yu1w8imesi/VgEpQM0deFYjV79XJJ5U3j0= X-Received: by 2002:a81:628b:0:b0:36b:9b28:384d with SMTP id w133-20020a81628b000000b0036b9b28384dmr9490425ywb.164.1666627030977; Mon, 24 Oct 2022 08:57:10 -0700 (PDT) MIME-Version: 1.0 References: <7f09c27c-c4df-4836-bd29-706ebc5eb154@Spark> In-Reply-To: <7f09c27c-c4df-4836-bd29-706ebc5eb154@Spark> From: Steve Midgley Date: Mon, 24 Oct 2022 08:57:01 -0700 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: select only 1 pair To: William Alves Da Silva Cc: pgsql-sql , Shaozhong SHI Content-Type: multipart/alternative; boundary="00000000000027936405ebc9d842" X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - premium22.web-hosting.com X-AntiAbuse: Original Domain - lists.postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - misuse.org X-Get-Message-Sender-Via: premium22.web-hosting.com: authenticated_id: science@misuse.org X-Authenticated-Sender: premium22.web-hosting.com: science@misuse.org X-Source: X-Source-Args: X-Source-Dir: X-From-Rewrite: unmodified, already matched List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000027936405ebc9d842 Content-Type: text/plain; charset="UTF-8" On Mon, Oct 24, 2022 at 8:02 AM William Alves Da Silva < william_silva@unochapeco.edu.br> wrote: > Hello David, > > Try this. > > This is an exemple of your table. > *SELECT* * > *FROM* (*VALUES* (1, 2), (2, 1), (3, 4), (4, 1)) t (id1, id2) > > id1 |id2 | > ------+------+ > 1| 2| > 2| 1| > 3| 4| > 4| 1| > > > I think that is what you need > *SELECT* *DISTINCT* *LEAST*(id1, id2) *AS* id1, *GREATEST*(id1, id2) *AS* > id2 > *FROM* (*VALUES* (1, 2), (2, 1), (3, 4), (4, 3)) t (id1, id2) > > id1 |id2 | > ------+------+ > 1| 2| > 3| 4| > > > I'll let OP clarify, but in your dataset example (not the same as original), shouldn't "4, 1" be found also. It's a unique pair (whereas "1,2" and "2,1" are repeating when sorted low/high). Which makes me think that merging the two columns into an array, sorting the array, and then squasing duplicates would do the job? Maybe there's an easier way, but from what I can see of the original requirements, your dataset should return the following? 1 | 2 3 | 4 4 | 1 Steve --00000000000027936405ebc9d842 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Mon, Oct 24, 2022 at 8:02 AM Willi= am Alves Da Silva <wi= lliam_silva@unochapeco.edu.br> wrote:
Hello David,

Try this.

This is an exemple of your table.
S= ELECT=C2=A0*=C2=A0
=C2= =A0=C2=A0FROM=C2=A0(VALUES=C2=A0(1, 2), (2, 1), (3,= 4), (4, 1))=C2=A0t=C2=A0(id1,=C2=A0id2)
=C2=A0=C2=A0
id1=C2=A0 =C2=A0|id2=C2=A0 =C2=A0|
------+------+
=C2=A0 =C2=A0 =C2=A01|=C2=A0 =C2=A0 =C2=A02|
=C2=A0 =C2=A0 =C2=A02|=C2=A0 =C2=A0 =C2= =A01|
=C2=A0 =C2=A0 =C2=A03|= =C2=A0 =C2=A0 =C2=A04|
=C2=A0 = =C2=A0 =C2=A04|=C2=A0 =C2=A0 =C2=A01|=C2=A0=C2=A0 =C2=A0 =C2=A0
=C2=A0 =C2=A0 =C2=A0

I think = that is what you need
SELECT= =C2=A0DISTINCT=C2=A0LEAST(id1,=C2=A0= id2)=C2=A0AS=C2=A0id1,=C2=A0GREATEST(id1,=C2=A0id2)=C2=A0AS=C2=A0id2
=C2=A0=C2=A0FROM=C2=A0(VALUES=C2=A0(1, 2), (2, 1), (3, 4), (4, 3))=C2=A0t=C2=A0(id1,=C2=A0id2)

id1=C2=A0 =C2=A0|id2= =C2=A0 =C2=A0|
------+------+=
=C2=A0 =C2=A0 =C2=A01|=C2=A0 = =C2=A0 =C2=A02|
=C2=A0 =C2=A0 = =C2=A03|=C2=A0 =C2=A0 =C2=A04|



I'll let OP cl= arify, but in your dataset example (not the same as original), shouldn'= t "4, 1" be found also. It's a unique pair (whereas "1,2= " and "2,1" are repeating when sorted low/high). Which makes= me think that merging the two columns=C2=A0into an array, sorting the arra= y, and then squasing duplicates would do the job? Maybe there's an easi= er way, but from what I can see of the original requirements, your dataset = should return the following?

1 | 2
3= | 4
4 | 1

Steve
--00000000000027936405ebc9d842--