oracle ROW_NUMBER()

Question

I would like to write a query to display this kind of results

column0	column1	column2	column3
a	x	1	1
a	y	1	2
a	z	1	3
b	x	2	1
b	y	2	2
c	x	3	1
c	y	3	2
c	z	3	3

column2 -> row number of column0

column3 -> row number of partition column0 & column1

I have tried this but not working

SELECT ROW_NUMBER() OVER (PARTITION BY column0 ORDER BY column0 ) column2,
       ROW_NUMBER() OVER (PARTITION BY column0 , column1  ORDER BY column0 , column1) column3  
FROM DUAL

have you got any ideas ?

Answer 1

Use DENSE_RANK for column2 and ROW_NUMBER for column3:

SELECT
    column0,
    column1,
    DENSE_RANK() OVER (ORDER BY column0) AS column3,
    ROW_NUMBER() OVER (PARTITION BY column0 ORDER BY column1) AS column4
FROM yourTable;