Skip to content
Advertisement

How to get incremental values running in another column

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);
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement