By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t SELECT 1 id, 1 contact_id, 1 organization_id
UNION ALL SELECT 2, 1, 1
UNION ALL SELECT 3, 2, 1
UNION ALL SELECT 4, 3, 1
UNION ALL SELECT 5, 4, 1
UNION ALL SELECT 6, 4, 1
UNION ALL SELECT 7, 4, 1;
SELECT ch.* FROM (SELECT MAX(c5_.id) AS sclr_32 FROM t c5_ WHERE (c5_.organization_id = '1') GROUP BY c5_.contact_id) tt JOIN t ch ON tt.sclr_32 = ch.id;
| id | contact_id | organization_id |
|---|---|---|
| 2 | 1 | 1 |
| 3 | 2 | 1 |
| 4 | 3 | 1 |
| 7 | 4 | 1 |
SELECT * FROM t WHERE id IN (SELECT MAX(c5_.id) AS sclr_32 FROM t c5_ WHERE (c5_.organization_id = '1') GROUP BY c5_.contact_id);
| id | contact_id | organization_id |
|---|---|---|
| 2 | 1 | 1 |
| 3 | 2 | 1 |
| 4 | 3 | 1 |
| 7 | 4 | 1 |