Using postgres, is it possible or how to select from table below distinct value of id and id2? :
| id | id2 | a | b | a1 | b1 |
|---|---|---|---|---|---|
| 1 | 2 | 150 | 200 | 200 | 200 |
| 2 | 2 | 150 | 200 | 200 | 200 |
| 3 | 2 | 150 | 200 | 200 | 200 |
| 1 | 4 | 150 | 200 | 200 | 200 |
| 2 | 4 | 150 | 200 | 200 | 200 |
| 3 | 4 | 150 | 200 | 200 | 200 |
I need to select distinct values of id, such as id2 is also distinct, and the differences between a and b, and then a1 and b2 are minimal.
For the example above, this should be only two rows, since there are only two distinct values in id2
Correct
| id | id2 | a | b | a1 | b1 |
|---|---|---|---|---|---|
| 1 | 2 | 150 | 200 | 200 | 200 |
| 2 | 4 | 150 | 200 | 200 | 200 |
I was trying to make it work via
distinct on(rank() over (order by b - a, a1 - b1, id)
Even if I try to select twice, like select over select, it doesn’t help much, so I’m a bit stuck.
For now I only got this, which is not correct:
Incorrect
| id | id2 | a | b | a1 | b1 |
|---|---|---|---|---|---|
| 1 | 2 | 150 | 200 | 200 | 200 |
| 1 | 4 | 150 | 200 | 200 | 200 |
with data as (
select 1 id, 1 as id2, 150 a, 200 b, 200 a1, 150 b1
union
select 1 id, 2 as id2, 150 a, 150 b, 200 a1, 200 b1
union
select 1 id, 3 as id2, 150 a, 100 b, 200 a1, 100 b1
union
select 1 id, 4 as id2, 150 a, 150 b, 200 a1, 200 b1
union
select 2 id, 1 as id2, 150 a, 200 b, 200 a1, 150 b1
union
select 2 id, 2 as id2, 150 a, 150 b, 200 a1, 200 b1
union
select 2 id, 3 as id2, 150 a, 100 b, 200 a1, 100 b1
union
select 2 id, 4 as id2, 150 a, 150 b, 200 a1, 200 b1
union
select 3 id, 1 as id2, 150 a, 200 b, 200 a1, 150 b1
union
select 3 id, 4 as id2, 150 a, 150 b, 200 a1, 200 b1
union
select 3 id, 3 as id2, 150 a, 100 b, 200 a1, 100 b1
union
select 3 id, 2 as id2, 150 a, 150 b, 200 a1, 200 b1
)
select distinct on (id2) *, rank() over (order by b - a, a1 - b1, id) rank, rank() over (order by b - a, a1 - b1, id)
from data
where b >= a and b1 >= a1
order by id2, b - a, a1 - b1, rank() over (order by id2, id);