public inbox for [email protected]
help / color / mirror / Atom feedFrom: Steve Midgley <[email protected]>
To: William Alves Da Silva <[email protected]>
Cc: pgsql-sql <[email protected]>
Cc: Shaozhong SHI <[email protected]>
Subject: Re: select only 1 pair
Date: Mon, 24 Oct 2022 08:57:01 -0700
Message-ID: <CAJexoSJLO31fthcjHqyQ+BoE-WYGq0mfX3fHN0uiWPb6r1VYug@mail.gmail.com> (raw)
In-Reply-To: <7f09c27c-c4df-4836-bd29-706ebc5eb154@Spark>
References: <CA+i5Jwa5VR7vJ=9S-9-d_3HgxQ_MmqwLW9+vqeN=jNDUVSVCHQ@mail.gmail.com>
<7f09c27c-c4df-4836-bd29-706ebc5eb154@Spark>
On Mon, Oct 24, 2022 at 8:02 AM William Alves Da Silva <
[email protected]> 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
view thread (7+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: select only 1 pair
In-Reply-To: <CAJexoSJLO31fthcjHqyQ+BoE-WYGq0mfX3fHN0uiWPb6r1VYug@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox