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 |