Oracle RANK() and DENSE_RANK() FUNCTION PRACTICE

-- Create a table named "rank_practice" containing student point information
create table rank_practice
(student_id number(5),
point number(10)
);

----------------------------------------------------------
-- Insert random point data to the table
----------------------------------------------------------
begin
for i in 1..28 loop
insert into rank_practice(student_id,point)
values(i,round(dbms_random.value('11','99')));
end loop;

insert into rank_practice(student_id,point) values(29,100);
insert into rank_practice(student_id,point) values(30,100);
commit;
end;
/

select * from rank_practice;
>>

STUDENT_IDPOINT
1 18
2 89
3 13
4 16
5 19
6 63
7 34
8 85
9 93
10 36
11 79
12 64
13 68
14 66
15 50
16 93
17 22
18 66
19 25
20 50
21 21
22 55
23 86
24 72
25 90
26 46
27 22
28 72
29 100
30 100

-----------------------------------------------------------------------------------
-- Using RANK() FUNCTION
-----------------------------------------------------------------------------------
select student_id,point,RANK() over (partition by null order by point desc) as rank
from rank_practice;
>>

STUDENT_IDPOINTRANK
29 100 1
30 100 1
16 93 3
9 93 3
25 90 5
2 89 6
23 86 7
8 85 8
11 79 9
28 72 10
24 72 10
13 68 12
18 66 13
14 66 13
12 64 15
6 63 16
22 55 17
15 50 18
20 50 18
26 46 20
10 36 21
7 34 22
19 25 23
27 22 24
17 22 24
21 21 26
5 19 27
1 18 28
4 16 29
3 13 30


-------------------------------------------------------------------------------------------------------------
-- Using DENSE_RANK() FUNCTION
-- which acts like the RANK function except that it assigns "consecutive" ranks
-------------------------------------------------------------------------------------------------------------
select student_id,point,DENSE_RANK() over (partition by null order by point desc) as rank
from rank_practice;
>>

STUDENT_IDPOINTRANK
29 100 1
30 100 1
16 93 2
9 93 2
25 90 3
2 89 4
23 86 5
8 85 6
11 79 7
28 72 8
24 72 8
13 68 9
18 66 10
14 66 10
12 64 11
6 63 12
22 55 13
15 50 14
20 50 14
26 46 15
10 36 16
7 34 17
19 25 18
27 22 19
17 22 19
21 21 20
5 19 21
1 18 22
4 16 23
3 13 24
arrow
arrow
    全站熱搜

    DanBrother 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄