How to create Sequencing in SQL when date are used as reference (Oracle
How can I create sequencing number in this table in SQL(oracle)?
(TABLE NAME: PAYXX)
ID Sequence Pay Date Pay Coverage Pay Coverage End
Start
ID101 7/25/2021 3/22/2021 6/27/2021
ID101 3/21/2021 3/8/2021 3/21/2021
ID101 5/2/2021 12/28/2020 3/21/2021
ID102 2/21/2021 2/8/2021 2/21/2021
ID102 3/7/2021 2/22/2021 3/7/2021
ID102 4/4/2021 3/22/2021 4/4/2021
ID103 7/25/2021 3/22/2021 6/27/2021
ID103 2/7/2021 1/25/2021 2/7/2021
ID103 2/21/2021 2/8/2021 2/21/2021
ID103 3/7/2021 2/22/2021 3/7/2021
ID103 3/7/2021 9/21/2020 12/27/2020
ID103 3/21/2021 3/8/2021 3/21/2021
It should look like this
ID Sequence Pay Date Pay Coverage Pay Coverage End
Start
ID101 3 7/25/2021 3/22/2021 6/27/2021
ID101 1 3/21/2021 3/8/2021 3/21/2021
ID101 2 5/2/2021 12/28/2020 3/21/2021
ID102 1 2/21/2021 2/8/2021 2/21/2021
ID102 2 3/7/2021 2/22/2021 3/7/2021
ID102 3 4/4/2021 3/22/2021 4/4/2021
ID103 5 7/25/2021 3/22/2021 6/27/2021
ID103 1 2/7/2021 1/25/2021 2/7/2021
ID103 2 2/21/2021 2/8/2021 2/21/2021
ID103 3 3/7/2021 2/22/2021 3/7/2021
ID103 3 3/7/2021 9/21/2020 12/27/2020
ID103 4 3/21/2021 3/8/2021 3/21/2021
You want DENSE_RANK
here. Assuming that the pay date be the column which determines the ranking within each ID
group:
SELECT t.*, DENSE_RANK() OVER (PARTITION BY ID ORDER BY pay_date) seq
FROM yourTable t
ORDER BY ID, pay_date;