Find best value - PostgreSQL

Question

I generated following Table:

  here  | there |      cost
 -------+-------+--------------+
  {1,1} | {1,2} |          0.0 | 
  {1,1} | {2,1} |          0.0 | 
  {1,2} | {1,3} |          1.0 |      
  {2,1} | {2,2} |          1.0 |
  {2,1} | {3,1} |          1.0 |
  {1,2} | {2,2} |          1.0 |      
  {1,3} | {1,4} |          2.0 |       
  {2,2} | {2,3} |          2.0 |       
 ...
  {1,2} | {1,3} |          3.0 |

As you can see, I have two different costs for the pair {1,2}, {1,3} How can i manage to keep just the best value for each pair ?

score 1 · Accepted Answer · answered Nov 18 '17 at 17:33

1

This query will return all unique pairs (here, there) with minimal cost:

SELECT
    here,
    there,
    MIN(cost)
FROM
    table_name
GROUP BY
    here,
    there;

answered Nov 18 '17 at 17:33

Adam

5,403
6
31
38

Erwin Brandstetter · Answer 2 · 2017-11-18T18:37:17.203

If you mean to keep the whole row (with more columns), use DISTINCT ON:

SELECT DISTINCT ON (here, there) *
FROM   tbl
ORDER  BY here, there, cost;

Select first row in each GROUP BY group?

Also, since you seem to be using arrays (table definition is undisclosed), careful about non-equality of '{1,2}' and '{2,1}'. If you need that to evaluate as equal, add your table definition and Postgres version to the question and I'll add a solution. Related:

Compare arrays for equality, ignoring order of elements

Find best value - PostgreSQL

2 Answers2