low_ghost low_ghost - 4 months ago 11
SQL Question

select first distinct values from two fields

This seems incredibly simple, but I'm just not quite putting it together. Say I have the values

a | b
---+---
1 | 10
1 | 20
2 | 10
2 | 20


How could I select from this table such that I get these values in a table:

a | b
---+---
1 | 10
2 | 20


The same would apply for:

a | b
---+---
1 | 10
1 | 20
1 | 30
2 | 10
2 | 20
2 | 30
3 | 10
3 | 20
3 | 30


to get

a | b
---+---
1 | 10
2 | 20
3 | 30


I've tried different combinations of distinct on and order by, but these won't work as it really does need to be distinct on both a and b. Maybe a window function...? Using postgres if it matters. Thanks!

Answer

I'm not sure distinct is the right term here, but if I understand correctly you have a set of values for a and b, and rows for each combination and you want to match the first a with the first b, the second a with the second b, etc. One way to do this is to use window functions to rank both columns, and then query only the rows with equals ranks:

SELECT a, b
FROM   (SELECT a, RANK() OVER (ORDER BY a) AS rank_a,
               b, RANK() OVER (ORDER BY b) AS rank_b
        FROM   myable) t
WHERE  rank_a = rank_b