low_ghost - 8 months ago 42

SQL Question

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
```