MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE IF NOT EXISTS `room_players`
(`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY  
,`player_id` int(11) NOT NULL  
,`room_id` tinyint(1) NOT NULL  
,`dealer` tinyint(1) NOT NULL
);

INSERT INTO room_players (`player_id`, `room_id`, `dealer`) VALUES
(1,    1,    '0'),
(2,    1,    '0'),
(3,    1,    '0'),
(4,    1,    '1'),
(7,    1,    '0'),
(99,    2,    '0'),
(14,    2,    '0'),
(22,    2,    '0'),
(77,    2,    '0'),
(15,    2,    '1');
SELECT * FROM room_players


id
player_id
room_id
dealer


1
1
1
0

2
2
1
0

3
3
1
0

4
4
1
1

5
7
1
0

6
99
2
0

7
14
2
0

8
22
2
0

9
77
2
0

10
15
2
1


SELECT *, 
       first_value(player_id) over (partition by room_id order by id desc) a,
       first_value(player_id) over (partition by room_id order by id) b,
       lead(player_id)  over (partition by room_id order by id)  c,
       
 case WHEN dealer=1 and player_id = first_value(player_id) over (partition by room_id order by id desc)  
              THEN first_value(player_id) over (partition by room_id order by id)
              WHEN dealer=1 
              THEN lead(player_id)  over (partition by room_id order by id) end  NextDealer
      From room_players


id
player_id
room_id
dealer
a
b
c
NextDealer


1
1
1
0
7
1
2
null

2
2
1
0
7
1
3
null

3
3
1
0
7
1
4
null

4
4
1
1
7
1
7
7

5
7
1
0
7
1
null
null

6
99
2
0
15
99
14
null

7
14
2
0
15
99
22
null

8
22
2
0
15
99
77
null

9
77
2
0
15
99
15
null

10
15
2
1
15
99
null
99


WITH CTE AS (
SELECT *, 
       first_value(player_id) over (partition by room_id order by id desc) a,
       first_value(player_id) over (partition by room_id order by id) b,
       lead(player_id)  over (partition by room_id order by id)  c,
       
 case WHEN dealer=1 and player_id = first_value(player_id) over (partition by room_id order by id desc)  
              THEN first_value(player_id) over (partition by room_id order by id)
              WHEN dealer=1 
              THEN lead(player_id)  over (partition by room_id order by id) end  NextDealer
      From room_players)
SELECT room_id, NextDealer FROM CTE WHERE NextDealer is not null


room_id
NextDealer


1
7

2
99

id	player_id	room_id	dealer	a	b	c	NextDealer
1	1	1	0	7	1	2	null
2	2	1	0	7	1	3	null
3	3	1	0	7	1	4	null
4	4	1	1	7	1	7	7
5	7	1	0	7	1	null	null
6	99	2	0	15	99	14	null
7	14	2	0	15	99	22	null
8	22	2	0	15	99	77	null
9	77	2	0	15	99	15	null
10	15	2	1	15	99	null	99