Oracle SQL value for repeated item

November 30, 2022

We have a list of orders that have multiple items, request is to have a value for each of the individual set of IDs. Like this:

order_id    id_number
ABC123      1
ABC123      1
DEF456      2
DEF456      2
DEF456      2
DEF456      2
GHI987      3

So that way we get a single number for each of the order_ids (and thus they can order the list using the id_number column).
Customer wants this to have an ‘easier’ view due to the amount of times an order can be repeated.
Is there a way to achieve this?

Thanks

>Solution :

That’s a good use case for DENSE_RANK:

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

Try out: db<>fiddle

Note: This answer assumes you are showing the expected outcome in your question because I understand it this way. If this is wrong, please make your question clear(er).