I have a sample Data like this
ID RN 1 1 1 2 1 3 1 4 1 5 1 6 1 7 500 8 500 9 500 10 500 11 861 12 861 13 861 14 853 15 843 16
I’m trying to get result like this
ID RN OP 1 1 NULL 1 2 1 1 3 1 1 4 1 1 5 1 1 6 1 1 7 1 500 8 NULL 500 9 8 500 10 8 500 11 8 861 12 NULL 861 13 12 861 14 12 853 15 NULL 843 16 NULL
I have tried with row number but not able to move forward.
Advertisement
Answer
If you’re not doing this with tens of thousands of records at a time, you could try something like this:
SELECT src.`id`, src.`rn`, (SELECT MIN(z.`rn`) FROM `Running` z WHERE z.`id` = src.`id` and z.`rn` < src.`rn`) as `op` FROM `Running` src ORDER BY src.`rn`;
This will give you the result based on this source data:
DROP TABLE IF EXISTS `Running`; CREATE TABLE IF NOT EXISTS `Running` ( `id` int(11) UNSIGNED NOT NULL , `rn` int(11) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `Running` (`id`, `rn`) VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (500, 8), (500, 9), (500, 10), (500, 11), (861, 12), (861, 13), (861, 14), (853, 15), (843, 16);