1. Prepare test data
---------------------------------------------------------------------------------
Create table t1 (
C1 integer,
C2 integer,
C3 integer
);
Insert into t1 values (1, 2, 3)
Insert into t1 values (1, 8, 4)
Insert into t1 values (1, 4)
Insert into t1 values (1, 4, 5)
Insert into t1 values (1, 5, 5)
Insert into t1 values (2, 2, 3)
Insert into t1 values (2, 8, 4)
Insert into t1 values (2, 4)
Insert into t1 values (2, 4, 5)
Insert into t1 values (2, 5, 5)
2/view rankings
---------------------------------------------------------------------------------
A/single Record ranking
Select c1, c3,
(Select count (c3) + 1 from t1 a where a. c3> B. c3
And a. c1 = B. c1 and a. c1 = 1
) Order_num
From t1 B
Where c1 = 1
Order by c1, c3
C1 c3 order_num
--------------------------------------------
1 3 5
1 4 3
1 4 3
1 5 1
1 5 1
B/Multi-Record ranking
Select c1, c2, c3,
(Select count (c3) + 1 from t1 a where a. c3> B. c3
And a. c1 = B. c1
) Order_num
From t1 B
Order by c1, c3
C1 c2 c3 order_num
-------------------------------------------------------
1 2 3 5
1 8 4 3
1 4 4 3
1 4 5 1
1 5 5 1
2 2 3 5
2 8 4 3
2 4 3
2 4 5 1
2 5 5 1