Re: select only 1 pair
| От | Steve Midgley |
|---|---|
| Тема | Re: select only 1 pair |
| Дата | |
| Msg-id | CAJexoSJLO31fthcjHqyQ+BoE-WYGq0mfX3fHN0uiWPb6r1VYug@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: select only 1 pair (William Alves Da Silva <william_silva@unochapeco.edu.br>) |
| Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: