MySQL 8.0

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

CREATE TABLE `table`
AS
SELECT 9 AS id 
UNION ALL SELECT 10
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
SELECT *
FROM `table`


id


9

10

1

2

3

4

5

6


SELECT *,  ROW_NUMBER() OVER(ORDER BY CASE WHEN id > 8 THEN 0 ELSE 1 END, id) AS rn
FROM `table`
ORDER BY rn LIMIT 6


id
rn


9
1

10
2

1
3

2
4

3
5

4
6