There are several ways to rank records based on certain variables. One of the is to use row_number
select * from TBL_TEST order by grp, num;
GRP NUM
---------- ----------
A 1
A 2
A 2
A 3
A 4
B 1
B 2
B 4
B 5
Row_number() generates unique sequece numbers without gap. Ties are given different numbers. I use row_number() to generate unique id.
Example 1. generate rank separately for GRP
with tbl as(
select a.grp, a.num, row_number() over(partition by GRP order by num) rnk from TBL_TEST a)
select * from tbl order by grp, rnk ;
GRP NUM RNK
---------- ---------- ----------
A 1 1
A 2 2
A 2 3
A 3 4
A 4 5
B 1 1
B 2 2
B 4 3
B 5 4
Example 2. Generate rank without partition by GRP
with tbl as(
select a.grp, a.num, row_number() over(order by grp, num) rnk from TBL_TEST a)
select * from tbl order by grp, rnk ;
GRP NUM RNK
---------- ---------- ----------
A 1 1
A 2 2
A 2 3
A 3 4
A 4 5
B 1 6
B 2 7
B 4 8
B 5 9
I will discuss rank functions including dense_rank(), ran() in another post.
Popular Topics
Popular Topics
Showing posts with label rank in Oracle. Show all posts
Showing posts with label rank in Oracle. Show all posts
Thursday, August 02, 2012
Subscribe to:
Posts (Atom)