Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table orders (order_id varchar(100));
insert into orders values ('ABC123');
1 rows affected

insert into orders values ('ABC123');
1 rows affected

insert into orders values ('DEF456');
1 rows affected

insert into orders values ('DEF456');
1 rows affected

insert into orders values ('DEF456');
1 rows affected

insert into orders values ('DEF456');
1 rows affected

insert into orders values ('GHI987');
1 rows affected

SELECT order_id, 
DENSE_RANK ()  
  OVER (ORDER BY order_id) AS id_number
FROM orders;


ORDER_ID
ID_NUMBER


ABC123
1

ABC123
1

DEF456
2

DEF456
2

DEF456
2

DEF456
2

GHI987
3