MySQL 8.0

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