Usage of DISTINCT in reversed int pairs duplicates elimination

Question

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

Answer 1

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.