How can I create a sub-index for rows in a SQL table sharing the same ID ordered by date

Question

I have a table where each row has columns for item IDs and dates. Id like to be able to add an additional column that will index each row sharing an ID by their date. For example, I have table 1, how to I get table 2?

Table 1:

ID	Date
1	3/2/2022
2	2/1/2022
1	12/18/2021
2	11/22/2021
3	10/6/2021
2	8/13/2021

Table 2:

ID	Date	IDX
1	3/2/2022	3
1	12/18/2021	2
2	11/22/2021	2
3	10/6/2021	1
2	8/13/2021	1
2	2/1/2022	3

Answer 1

You haven't said what kind of SQL you're using. If you can use RANK() can do what you are needing.

create table s (
 id int,
 dat date);
insert into s values
(1,'2020-01-01'),
(2,'2020-01-02'),
(1,'2020-01-03'),
(2,'2020-01-04'),
(3,'2020-01-05'),
(2,'2020-01-06');

select
  id,
  dat "date",
  rank() over 
    (partition by id
     order by dat) ranking
from s
order by id, dat;

id | date       | ranking
-: | :--------- | ------:
 1 | 2020-01-01 |       1
 1 | 2020-01-03 |       2
 2 | 2020-01-02 |       1
 2 | 2020-01-04 |       2
 2 | 2020-01-06 |       3
 3 | 2020-01-05 |       1

db<>fiddle here