Usage of DISTINCT in reversed int pairs duplicates elimination

user_11251373 0
Viewed 95

I have a following question:

create table memorization_word_translation
(
    id           serial  not null
    from_word_id integer not null
    to_word_id   integer not null
);

This table stores pairs of integers, that are often in reverse order, for example:

35 36
35 37
36 35
37 35
37 39
39 37

Question is - if I make a query, for example:

select * from memorization_word_translation
where from_word_id = 35 or to_word_id = 35

I would get

35 36
35 37
36 35  - duplicate of 35 36
37 35  - duplicate of 35 37

How is to use DISTINCT in this example to filter out all duplicates even if they are reversed? I want to keep it only like this:

35 36
35 37
distinct postgresql
2 Answers

You can do it with ROW_NUMBER() window function:

select from_word_id, to_word_id
from (
  select *,
         row_number() over (
           partition by least(from_word_id, to_word_id), 
                        greatest(from_word_id, to_word_id)
           order by (from_word_id > to_word_id)::int
         ) rn                                                         
  from memorization_word_translation 
  where 35 in (from_word_id, to_word_id)
) t
where rn = 1

See the demo.

user_104988280

demo:db<>fiddle

You could try a it with a small sorting algorithm (here a comparison) in combination with DISTINCT ON.

The DISTINCT ON clause works an arbitrary columns or terms, e.g. on a tuple. This CASE clause sorts the two columns into tuples and removes tied (ordered) ones. The source columns can be returned in your SELECT statement:

select distinct on (
    CASE 
        WHEN (from_word_id >= to_word_id) THEN (from_word_id, to_word_id)
        ELSE (to_word_id, from_word_id)
    END 
)
    * 
from memorization_word_translation
where from_word_id = 35 or to_word_id = 35
user_39842210
Related