ROW_NUMBER () OVER (PARTITION BY) functionality in oracle 12c

Usage of ROW_NUMBER () OVER (PARTITION BY) functionality in oracle 12c.

This is use for main two purpose

1) It assign new index for row as per repeat Field in Table

2) While you want to convert row to Column (Each row can be convert in Column for another table)

EX:  I have Data table EMP as Follows..

partition1

Query with PARTITION:

SELECT EMP.EMPNO,
EMP.EMPNAME,
EMP.EMP_SAL,
ROW_NUMBER() OVER( PARTITION BY EMP.EMPNO,EMP.EMPNAME ORDER BY EMP.EMP_SAL) SEQ
FROM KALPESH.EMP EMP
ORDER BY SEQ

This will return result based on repetition of Field PARTITION BY EMP.EMPNO,EMP.EMPNAME as follow..

partition2

Use Result to make column For Actual result.

Query:

SELECT MAX(DECODE(EMP1.SEQ, 1, EMP1.EMPNO)) First,
MAX(DECODE(EMP1.SEQ, 2, EMP1.EMPNO)) Second,
MAX(DECODE(EMP1.SEQ, 3, EMP1.EMPNO)) Third
FROM (
SELECT EMP.EMPNO,
EMP.EMPNAME,
EMP.EMP_SAL,
ROW_NUMBER() OVER( PARTITION BY EMP.EMPNO,EMP.EMPNAME ORDER BY EMP.EMP_SAL) SEQ
FROM KALPESH.EMP EMP
ORDER BY SEQ
)EMP1

Output of Above Query:

partition3

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s