Oracle SQL value for repeated item-CodePudding

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

CodePudding user response：

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).

CodePudding user response：

select   ORDER_ID
        ,ID_NUMBER
        ,count(*) as ORDER_COUNT
from     t
group by ORDER_ID, ID_NUMBER

ORDER_ID	ID_NUMBER	ORDER_COUNT
ABC123	1	2
DEF456	2	4
GHI987	3	1

Fiddle