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;

Answer 2

I dont think you can make it without scanning table twice:

t=# with g as (select src,dest,min(length) from t1 group by src,dest)
select t1.* from t1
join g on t1.src = g.src and t1.dest = g.dest and length = min
;
 src | dest | length | path
-----+------+--------+------
 a   | d    |      2 | acd
 a   | d    |      2 | abd
 a   | e    |      3 | ace
 a   | g    |      6 | acg
(4 rows)