两列的不同组合，从第三列排序?

Question

假设我有以下表格:

col1        col2        diff
ABC         XYZ         1.2
FOO         BAR         5.0
FOO         BAR         0.0
ABC         XYZ         1.3

现在我想要得到col1和col2的唯一组合，但对于每个组合，我只想要具有最低diff的那个。所以我要找的结果是:

col1        col2        diff
ABC         XYZ         1.2
FOO         BAR         0.0

我可以做DISTINCT col1, col2或GROUP BY col1, col2，但我如何确保我得到具有最低diff的组合?

Answer 1

尝试按col1和col2聚合，并选择diff的最小值:

SELECT col1, col2, MIN(diff) AS diff
FROM yourTable
GROUP BY col1, col2;