ROW_NUMBER oracle

ROW_NUMBER is an example of analytical function. It returns a unique number for each record.

Consider below emp table data

EMPNO EMPNAME SALARY DEPTNO
10 Bill 12000 5
11 Solomon 10000 5
12 Susan 10000 5
13 Wendy 9000 1
14 Benjamin 7500 1
15 Tom 7600 1
16 Henry 8500 2
17 Robert 9500 2
18 Paul 7700 2

ROW_NUMBER()

Select empno, deptno, salary, row_number() over(order by salary) from emp;

ROW_NUMBER assigns a unique number by ordering by salary ascending by default

ROW_NUMBER simple example
ROW_NUMBER simple example

You can use row_number to find top N records

Below query returns first 3rd to 5th records

select * from
(
select empno, deptno, salary, row_number() over(order by salary) as rn from emp
) where rn between 3 and 5
;

ROW_NUMBER() with partition by clause

select empno, deptno, salary,
row_number() over(partition by deptno order by salary)
from emp;

Returns unique number within the window group, ie deptno. Each department it will find the employees based on their salary.

Row_number_partitionby.PNG
ROW_NUMBER with partition by clause

ROW_NUMBER() with NULLS LAST

This ignores the records having salary as null and give least priority for ranking

Select empno, deptno, salary,
row_number() over(order by salary nulls last)
from emp;

ROW_NUMBER with NULLS LAST
ROW_NUMBER with NULLS LAST
Advertisements

2 thoughts on “ROW_NUMBER oracle

  1. Hi sir ,
    as you know many guys are following this blog am also one among them this is really good for students who really want to learn from scratch thanks for providing all these stuff.

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