PostgreSQL: select rows with min value in one column among rows with identical columns

Question

The description in tilte may not be exactly what I want. Here is an example. Given a table t1:

src dest length path
 a    e    5    a b e
 a    d    2    a c d
 a    g    6    a c g
 a    e    3    a c e
 a    e    4    a g e
 a    d    2    a b d

For each (src, dest) pair, if there is only one entry, keep it; if there are multiple entries, select the one has the minimum length, if their length are the same, keep all of them. The output should be:

src dest length path
 a   d     2    a c d
 a   g     6    a c g
 a   e     3    a c e
 a   d     2    a b d

How can I approach it using PostgreSQL?

Answer 1

I would use window functions:

select t.*
from (select t.*,
             dense_rank() over (partition by src, dest order by length) as seqnum
      from t
     ) t
where seqnum = 1;