How to use Oracle Rank function sharing _oracle

In Oracle, there are Rank,dense_rank,row_number, and group ranking partition.

Description

Rank: The rank will appear in parallel with nth name, after which it will skip the vacated position, for example: 1,2,2,4
Dense_rank: Ranking will appear in parallel with the nth name, it followed the rank of n+1, for example: 1,2,2,3
Row_number: Ranking with unique sequential values, such as 1,2,3,4
Partition: Limit rankings to a group

Format:

Row_number () over (partition by Bb.channel_name ORDER by sum (aa.dk_serv_num) desc nulls) P1_rank1,
Row_number () over (order by sum (aa.dk_serv_num) desc nulls last) Rank1,
Dense_rank () over (order by NVL (sum (aa.dk_serv_num), 0) desc) rank2,
Rank () over (order by sum (aa.dk_serv_num) desc nulls last) Rank3

Example:

Procedure getcompetitionranking (P_userid in-Integer, P_competitionid in-Integer, v_cursor out competitioncursor) is V_sta
Rtdate date;
V_enddate date;
Tmp_startdate VARCHAR2 (12);
Tmp_enddate VARCHAR2 (12);
Tmp_date date;
V_SQL1 VARCHAR2 (2000);
V_SQL2 VARCHAR2 (2000);

V_where VARCHAR2 (1000);
V_comtotal integer;
V_grouptotal integer;
V_comranking integer;
V_groupranking integer; Begin select T.start_date, t.end_date into V_startdate, v_enddate from tbl_competition t where t.competition_id = P_compet

Itionid;
Tmp_date:= v_enddate+1;
Tmp_startdate: = To_char (v_startdate, ' yyyy-mm-dd ');

Tmp_enddate: = To_char (tmp_date, ' yyyy-mm-dd '); --group Personal Total Select count (1) to v_grouptotal from Tbl_com_group_user a where a.com_group_id in (select B.com_

group_id from Tbl_com_group_user b where b.user_id = P_userid); --Competition Personal Total Select count (1) to V_comtotal from (select a.com_group_id from Tbl_com_group a where a.co mpetition_id = P_competitionid) a INNER join tbl_com_group_user b on a.com_group_id = b.com_group_id; --user in competition ranking and group ranking v_where: = ' t.data_type_id=1 and T.status=1 and T.data_date_1 >= E (' | | Chr (39) | | tmp_startdate| | Chr (39) | | ', ' | | Chr (39) | | Yyyy-mm-dd ' | | Chr (39) | | ') and T.data_date_1 < To_date (' | | Chr (39) | | tmp_enddate| | Chr (39) | | ', ' | | Chr (39) | | Yyyy-mm-dd ' | | Chr (39) | | ')

';  /*select No from (select a.user_id, Dense_rank () over (order by sum (NVL (data_number_2, 0)) desc) No from (select user_id From Tbl_com_group a LEFT join Tbl_com_group_user b on a.com_group_id = b.com_group_id where a.competition_id = 1) a lef T join Vm_master_data t on a.user_id = t.user_id and t.data_type_id=1 and T.status=1 and T.data_date_1 >= TO_DATE (' 2012 -10-02 ', ' yyyy-mm-dd ') and T.data_date_1 < To_date (' 2012-12-01 ', ' Yyyy-mm-dd ') group by a.user_id ORDER BY no desc) whe Re user_id = 165*/v_sql1: = ' Select No from (select a.user_id, Dense_rank () over (order by sum (NVL (data_number_2, 0)) de SC) No from (select user_idFrom Tbl_com_group a LEFT join Tbl_com_group_user b on a.com_group_id = b.com_group_id where a.competition_id = ' | | p_competitionid| | ') A LEFT join Vm_master_data t on a.user_id = t.user_id and ' | | v_where| | ' GROUP by a.user_id ORDER BY no desc ' where user_id = ' | |

P_userid;
Dbms_output.put_line (V_SQL1);
Execute immediate V_SQL1 into v_comranking;
Dbms_output.put_line ('------------------------------');

--dbms_output.put_line (v_comranking); /*select No from (select a.user_id, Dense_rank () over (order by sum (NVL (data_number_2, 0)) desc) No from (select A.user _id from Tbl_com_group_user a where a.com_group_id into (select b.com_group_id from Tbl_com_group_user b where b.user_id = 165)) A LEFT join Vm_master_data t on a.user_id = t.user_id and t.data_type_id=1 and T.status=1 and T.data_date_1 To_date (' 2012-10-02 ', ' yyyy-mm-dd ') and T.data_date_1 < To_date (' 2012-12-01 ', ' Yyyy-mm-dd ') group by a.user_id Order By no desc where user_id=165*/v_sql2: = ' Select No from (select a.user_id, Dense_rank () over (order by sum (NVL (data_number_2, 0)) desc) No from (select a.user_id from Tbl_com_group_user A Where a.com_group_id in (select b.com_group_id from Tbl_com_group_user b where b.user_id = ' | |
p_userid| | ') A LEFT join Vm_master_data t on a.user_id = t.user_id and ' | | v_where| | ' GROUP by a.user_id ORDER BY no desc ' where user_id = ' | |

P_userid;
Dbms_output.put_line (V_SQL2);
Execute immediate v_sql2 into v_groupranking;
--dbms_output.put_line ('------------------------------');

--dbms_output.put_line (v_groupranking);
If v_comranking is null then v_comranking: = V_comtotal;

End If;
If v_groupranking is null then v_groupranking: = V_grouptotal;

End If; Open v_cursor for select V_comtotal competitionpersonaltotal, V_grouptotal useringrouppersontotal, v_comRanking

Userincompranking, v_groupranking useringroupranking from dual;
Exception when others then null; End