Duplicated rows numbering

Question

I need to number the rows so that the row number with the same ID is the same. For example:

Oracle database. Any ideas?

Answer 1

Use the DENSE_RANK analytic function:

SELECT DENSE_RANK() OVER (ORDER BY id) AS row_number,
       id
FROM   your_table

Which, for the sample data:

CREATE TABLE your_table ( id ) AS
SELECT 86325 FROM DUAL UNION ALL
SELECT 86325 FROM DUAL UNION ALL
SELECT 86326 FROM DUAL UNION ALL
SELECT 86326 FROM DUAL UNION ALL
SELECT 86352 FROM DUAL UNION ALL
SELECT 86353 FROM DUAL UNION ALL
SELECT 86354 FROM DUAL UNION ALL
SELECT 86354 FROM DUAL;

Outputs:

ROW_NUMBER	ID
1	86325
1	86325
2	86326
2	86326
3	86352
4	86353
5	86354
5	86354

db<>fiddle here