Date: Mon, 24 Oct 2022 17:01:54 +0200
From: Frank Streitzig <fstreitzig@gmx.net>
To: Shaozhong SHI <shishaozhong@gmail.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: select only 1 pair
Message-ID: <Y1ao4uH2OG8d5N96@frastr-dev>
References: 
 <CA+i5Jwa5VR7vJ=9S-9-d_3HgxQ_MmqwLW9+vqeN=jNDUVSVCHQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CA+i5Jwa5VR7vJ=9S-9-d_3HgxQ_MmqwLW9+vqeN=jNDUVSVCHQ@mail.gmail.com>
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/Y1ao4uH2OG8d5N96%40frastr-dev>
Precedence: bulk

Am Mon, Oct 24, 2022 at 03:44:03PM +0100 schrieb Shaozhong SHI:
> There are pair ids.  Each pair is repeated.
>
> id1   id2
> 1       2
> 2        1
> 3         4
> 4         3
>
> How to only select 1 unique pair for each?
>
> Regards,
> David

Hello,

if just 2 id's then sort with min and max comparing.
Example:

with data (id1, id2) as (
    values (1,2), (2,1), (3,4), (4,3)
)
select case when id1 <=3D id2 then id1 else id2 end as idmin
       , case when not id1 <=3D id2 then id1 else id2 end as idmin
    from data
    group by 1, 2
;

Best regards,
Frank