MySQL 8.0

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

CREATE TABLE toys (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(50) NOT NULL,
  rank_index TINYINT UNSIGNED,
  created_at DATETIME NOT NULL
);
INSERT INTO toys VALUES
  (NULL, 'An awesome product', NULL, '2023-01-01 10:04:00'),
  (NULL, 'Another product',       4, '2023-01-01 10:00:00'),
  (NULL, 'Baby car',           NULL, '2023-01-01 10:05:00'),
  (NULL, 'Green carpet',          2, '2023-01-01 10:08:00'),
  (NULL, 'Toy',                NULL, '2023-01-01 10:07:00');

Records: 5  Duplicates: 0  Warnings: 0


SELECT * FROM toys;


id
title
rank_index
created_at


1
An awesome product
null
2023-01-01 10:04:00

2
Another product
4
2023-01-01 10:00:00

3
Baby car
null
2023-01-01 10:05:00

4
Green carpet
2
2023-01-01 10:08:00

5
Toy
null
2023-01-01 10:07:00


WITH RECURSIVE seq (n) AS (
    SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < (SELECT COUNT(*) FROM toys)
)
SELECT title, rank_index, created_at, n
FROM (
    SELECT seq.n, ROW_NUMBER() OVER (ORDER BY seq.n) AS rn
    FROM seq
    WHERE NOT EXISTS (SELECT 1 FROM toys WHERE rank_index = seq.n)
) x
JOIN (
    SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC) rn 
    FROM toys
    WHERE rank_index IS NULL
) y USING (rn)

UNION ALL

SELECT title, rank_index, created_at, rank_index
FROM toys
WHERE rank_index IS NOT NULL

-- applies to the result of the UNION
ORDER BY n;


title
rank_index
created_at
n


Toy
null
2023-01-01 10:07:00
1

Green carpet
2
2023-01-01 10:08:00
2

Baby car
null
2023-01-01 10:05:00
3

Another product
4
2023-01-01 10:00:00
4

An awesome product
null
2023-01-01 10:04:00
5


-- this cte just gives is a contiguous sequence from 1 to number of toys
WITH seq (n) AS (
    SELECT ROW_NUMBER() OVER (ORDER BY id) FROM toys
)
SELECT title, rank_index, created_at, n
FROM (
    -- we now add row_number to the seq after removing the seq numbers
    -- already used by rank_index
    SELECT seq.n, ROW_NUMBER() OVER (ORDER BY seq.n) AS rn
    FROM seq
    WHERE NOT EXISTS (SELECT 1 FROM toys WHERE rank_index = seq.n)
) x
JOIN (
    -- get toys without rank_index and add row_number for join to prev subquery
    SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC) rn 
    FROM toys
    WHERE rank_index IS NULL
) y USING (rn)

UNION ALL

SELECT title, rank_index, created_at, rank_index
FROM toys
WHERE rank_index IS NOT NULL

-- applies to the result of UNION
ORDER BY n;


title
rank_index
created_at
n


Toy
null
2023-01-01 10:07:00
1

Green carpet
2
2023-01-01 10:08:00
2

Baby car
null
2023-01-01 10:05:00
3

Another product
4
2023-01-01 10:00:00
4

An awesome product
null
2023-01-01 10:04:00
5

id	title	rank_index	created_at
1	An awesome product	null	2023-01-01 10:04:00
2	Another product	4	2023-01-01 10:00:00
3	Baby car	null	2023-01-01 10:05:00
4	Green carpet	2	2023-01-01 10:08:00
5	Toy	null	2023-01-01 10:07:00