By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1(
odering_id int auto_increment primary key,
id INTEGER NOT NULL
,ord INTEGER NOT NULL
);
INSERT INTO table1(id,ord) VALUES (122,6);
INSERT INTO table1(id,ord) VALUES (122,4);
INSERT INTO table1(id,ord) VALUES (122,3);
INSERT INTO table1(id,ord) VALUES (123,6);
INSERT INTO table1(id,ord) VALUES (123,5);
INSERT INTO table1(id,ord) VALUES (123,3);
INSERT INTO table1(id,ord) VALUES (124,6);
INSERT INTO table1(id,ord) VALUES (124,5);
INSERT INTO table1(id,ord) VALUES (125,6);
INSERT INTO table1(id,ord) VALUES (125,5);
CREATE TABLE table2(
id INTEGER NOT NULL PRIMARY KEY
);
INSERT INTO table2(id) VALUES (122);
INSERT INTO table2(id) VALUES (123);
INSERT INTO table2(id) VALUES (124);
select t2.id,
coalesce(
nullif((select ord from table1 t1 where t1.id = t2.id order by odering_id desc limit 1), 3),
(select ord from table1 t1 where t1.id = t2.id order by odering_id desc limit 1, 1)
) as ord
from table2 t2
| id | ord |
|---|---|
| 122 | 4 |
| 123 | 5 |
| 124 | 5 |