Wednesday, March 6, 2013

window function < rank() over >in oracle

select * from pt_test


NO,NAME
-21,hello
4,hello
1,hello2
4,hello2
5,hello
10,hello


select no, name, rank() over(order by no desc) all_rank,
 rank() over(partition by name  order by no desc) no_rank 
from pt_Test


NO,NAME,ALL_RANK,ALL_RANK_1
10,hello,1,1
5,hello,2,2
4,hello,3,3
4,hello2,3,1
1,hello2,5,2
-21,hello,6,4

No comments:

Post a Comment