CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Tony
Tony

Reputation: 644

Find best value - PostgreSQL

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 ?

Upvotes: 0

Views: 96

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656401

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;

Related:

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:

Upvotes: 0

Adam
Adam

Reputation: 5599

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

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

Upvotes: 1

Related Questions