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 1omyxa-0001DK-OY for pgsql-sql@arkaria.postgresql.org; Mon, 24 Oct 2022 15:01:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1omyxZ-0003Bz-JF for pgsql-sql@arkaria.postgresql.org; Mon, 24 Oct 2022 15:01:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1omyxX-00038Q-Pt for pgsql-sql@lists.postgresql.org; Mon, 24 Oct 2022 15:01:49 +0000 Received: from mail-oa1-x32.google.com ([2001:4860:4864:20::32]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1omyxT-0006Ai-9J for pgsql-sql@lists.postgresql.org; Mon, 24 Oct 2022 15:01:47 +0000 Received: by mail-oa1-x32.google.com with SMTP id 586e51a60fabf-13ae8117023so12139047fac.9 for ; Mon, 24 Oct 2022 08:01:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=unochapeco.edu.br; s=google; h=mime-version:subject:references:in-reply-to:message-id:to:from:date :from:to:cc:subject:date:message-id:reply-to; bh=S8BwKrPdHUEVwhcomCGmM6lMaS1Sas5S7ICW4XUHEEQ=; b=GJqGgkTUDZHVHHgUralKnBEdNrm2nCteDnkGdz+iQf+akjrmfhGkDrAJBdrH8CgTtk 9CB+DXTQa3IyHdC5Sw/CQMYrWQZSK0UqftgoQp87zNO8VQ8ofSyplZCIYUs0JdWEt9U+ xK6L6g21zeObZiE+0fUP1XMgRWc5bP3Dfe4tFffb3ONx8KlhguJmIblHRdWF3tSnCwuC whfgrnqcQoUdpLw0/oWgFyosPhO4Ho4UkpVF9aAa4NfzFmfd6RDk+tC3aQNrOU8WMNwV 3DKYvW0bDxK6fyVFZzpdJ/HQjLPtWsn/+hq0F5Fi82F5f4EqGBkyYE1n36DVkG3O8gYw aAOw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=mime-version:subject:references:in-reply-to:message-id:to:from:date :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=S8BwKrPdHUEVwhcomCGmM6lMaS1Sas5S7ICW4XUHEEQ=; b=UZ2ExBJYAuMMj1DwvzflQOu4NJkIZfUeuRX9mZt+eTPedNIDXZetnduKnKhUJg6QRS ayyOSek7FigTrG86o2VcUaEACqd1dHPIIXBdT+eYcSFCjpuUYYcuFYc0AcOSIUmKZEMa plcjUzXm+IdBNMGQ8BhPCFPkcQArkiVztZI8jlSe6z6sBwqVd+QvYdIRZcSqGYe5lE7V eIG63Dd4TST5GsmOxAZAEGWMgHU1NEHLZf1Kxn0vyCTB6aemw2V/FdeWDKcv3rzlbxmW c8v+R4lwggfCt3ry7opJQADBV4DDyJsY4K4kz2cmEKArJVJby2g7Na5EMDELJGct6BOu ihxQ== X-Gm-Message-State: ACrzQf1aT+S/gZAXrgDTbOGYh85w9ebOBMUkejgSMnhNDBZhPp5VYdqW zdEEVWMc/Wkl7WuHCLT1hGBy5NTaqh7ixIoK14Rk2QT/5m6zYDjbIIkKt3h0e7f3yFFO64gs1oK N/hZgUB1xjwKYr5kmu4E2M4gsb202MiCcTyOP3XyYLFzsUd8JzIGU5bOxcUNO1hiJjB6wEKJVb2 WJq5O8UzxqFafrg3gxvvA= X-Google-Smtp-Source: AMsMyM4dnpwq3kaZZLEUDVRprt7xhvptu0/sZWqJf9os6K1eQB/eKbzAvca874EuC4sEMVXLcrSgXg== X-Received: by 2002:a05:6870:4389:b0:13b:875e:510f with SMTP id r9-20020a056870438900b0013b875e510fmr4809177oah.82.1666623700588; Mon, 24 Oct 2022 08:01:40 -0700 (PDT) Received: from [192.168.15.183] ([177.156.184.131]) by smtp.gmail.com with ESMTPSA id s6-20020a056870ea8600b0010bf07976c9sm13946423oap.41.2022.10.24.08.01.39 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 24 Oct 2022 08:01:40 -0700 (PDT) Date: Mon, 24 Oct 2022 12:01:30 -0300 From: William Alves Da Silva To: pgsql-sql , Shaozhong SHI Message-ID: <7f09c27c-c4df-4836-bd29-706ebc5eb154@Spark> In-Reply-To: References: Subject: Re: select only 1 pair X-Readdle-Message-ID: 7f09c27c-c4df-4836-bd29-706ebc5eb154@Spark MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="6356a8cf_62bbd95a_23f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --6356a8cf_62bbd95a_23f Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hello David, Try this. This is an exemple of your table. SELECT=C2=A0* =C2=A0=C2=A0=46ROM=C2=A0(VALUES=C2=A0(1, 2), (2, 1), (3, 4), (4, 1))=C2=A0= t=C2=A0(id1,=C2=A0id2) id1=C2=A0 =C2=A0=7Cid2=C2=A0 =C2=A0=7C ------+------+ =C2=A0 =C2=A0 =C2=A01=7C=C2=A0 =C2=A0 =C2=A02=7C =C2=A0 =C2=A0 =C2=A02=7C=C2=A0 =C2=A0 =C2=A01=7C =C2=A0 =C2=A0 =C2=A03=7C=C2=A0 =C2=A0 =C2=A04=7C =C2=A0 =C2=A0 =C2=A04=7C=C2=A0 =C2=A0 =C2=A01=7C I think that is what you need SELECT=C2=A0DISTINCT=C2=A0LEAST(id1,=C2=A0id2)=C2=A0AS=C2=A0id1,=C2=A0GRE= ATEST(id1,=C2=A0id2)=C2=A0AS=C2=A0id2 =C2=A0=C2=A0=46ROM=C2=A0(VALUES=C2=A0(1, 2), (2, 1), (3, 4), (4, 3))=C2=A0= t=C2=A0(id1,=C2=A0id2) id1=C2=A0 =C2=A0=7Cid2=C2=A0 =C2=A0=7C ------+------+ =C2=A0 =C2=A0 =C2=A01=7C=C2=A0 =C2=A0 =C2=A02=7C =C2=A0 =C2=A0 =C2=A03=7C=C2=A0 =C2=A0 =C2=A04=7C Regards, William Alves On 24 Oct 2022 11:44 -0300, Shaozhong SHI , wro= te: > There are pair ids.=C2=A0 Each pair is repeated. > > id1=C2=A0 =C2=A0id2 > 1=C2=A0 =C2=A0 =C2=A0 =C2=A02 > 2=C2=A0 =C2=A0 =C2=A0 =C2=A0 1 > 3=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A04 > 4=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A03 > > How to only select 1 unique pair for each=3F > > Regards, > > David --6356a8cf_62bbd95a_23f Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Hello David,

Try this.

This is an exemple of your table.
S= ELECT&=23160;*&=23160;&=23160;&=23160;=46ROM&=23160;(VALUES&=231= 60;(1, 2), (2, 1), (3, 4), (4, 1))&=23160;t&=23160;(id1<= span style=3D=22color:=23aaaaaa;font-family:Menlo;font-size: 12px=22>,&=23= 160;id2)
<= span style=3D=22color:=23aaaaaa;font-family:Menlo;font-size: 12px=22>&=23= 160;&=23160;
id1&= =23160; &=23160;=7Cid2&=23160; &=23160;=7C
------+------+
&=23160; &=23160; &=23160;1=7C&=23160; &=23160; &=23= 160;2=7C
&=23160; &=23= 160; &=23160;2=7C&=23160; &=23160; &=23160;1=7C
&=23160; &=23160; &=23160;3=7C&=23160; &=23160= ; &=23160;4=7C
&=2316= 0; &=23160; &=23160;4=7C&=23160; &=23160; &=23160;1=7C&=23160;&=23160; &=23= 160; &=23160;
&=23160= ; &=23160; &=23160;

I think that is what you = need
SELECT&=23= 160;DISTINCT&=23160;LEAST(id1,&=23160;= id2)&=23160;AS&=23160;id1,&=23160;GREATEST<= /strong>(id1,&=23160;id2)&=23160;AS&=23160;i= d2
&=23160;&=23160;=46ROM&=23160;(VALUES&=23160;(1, 2), (2, 1), (3, 4), (= 4, 3))&=23160;t&=23160;(id1,&=23160;id2)

id1&=23160; &=23160;=7Cid2&=23160; &=23160;= =7C
------+------+
&=23160; &=23160; &=231= 60;1=7C&=23160; &=23160; &=23160;2=7C
&=23160; &=23160; &=23160;3=7C&=23160; &=23160; &=23160;= 4=7C

Regards,


William Alves

On 24 Oct 2022 11:44 -0300, Shaozho= ng SHI <shishaozhong=40gmail.com>, wrote:
There are pair ids.&=23160; Each pair is repeated.

id1&=23160; &=23160;id2
1&=23160; &=23160; &=23160; &=23160;2
2&=23160; &=23160; &=23160; &=23160; 1
3&=23160; &=23160; &=23160; &=23160; &=23160;4
4&=23160; &=23160; &=23160; &=23160; &=23160;3

How to only select 1 unique pair for each=3F

Regards,

David
--6356a8cf_62bbd95a_23f--