With this query:
with tbl as
(
select 1 ord, 'A' name from dual
union all
select 2 ord, 'A' name from dual
union all
select 3 ord, 'A' name from dual
union all
select 4 ord, 'B' name from dual
union all
select 5 ord, 'B' name from dual
union all
select 6 ord, 'A' name from dual
union all
select 7 ord, 'A' name from dual
union all
select 8 ord, 'C' name from dual
union all
select 9 ord, 'C' name from dual
union all
select 10 ord, 'B' name from dual
union all
select 11 ord, 'B' name from dual
union all
select 12 ord, 'B' name from dual
)
select ord, name, myrank(...)
from tbl
order by
ord;
I want to get these results:
ORD NAME MYRANK
---------- ---- ----------
1 A 1
2 A 1
3 A 1
4 B 2
5 B 2
6 A 3
7 A 3
8 C 4
9 C 4
10 B 5
11 B 5
12 B 5
Same rank for continuous equal values. Different groups of same continuous equal values have different ranks. Rank increases monotonically in order of "ord".
For Oracle and PostgreSQL (ultimate query for both systems is preferable).
Edit: I forgot to mention version: Oracle 11g PostgreSQL 12
From Oracle 12, you can use MATCH_RECOGNIZE
to perform row-by-row pattern matching:
SELECT ord, name, myrank
FROM tbl
MATCH_RECOGNIZE(
ORDER BY ord
MEASURES
MATCH_NUMBER() AS myrank
ALL ROWS PER MATCH
PATTERN (same_name+)
DEFINE
same_name AS FIRST(name) = name
);
In all versions, you can use the LAG
and SUM
analytic functions to output the same:
SELECT ord,
name,
SUM(has_changed) OVER (ORDER BY ord) AS myrank
FROM (
SELECT ord,
name,
CASE
WHEN name = LAG(name) OVER (ORDER BY ord)
THEN 0
ELSE 1
END AS has_changed
FROM tbl
) t;
Which, for the sample data, both output:
ORD | NAME | MYRANK |
---|---|---|
1 | A | 1 |
2 | A | 1 |
3 | A | 1 |
4 | B | 2 |
5 | B | 2 |
6 | A | 3 |
7 | A | 3 |
8 | C | 4 |
9 | C | 4 |
10 | B | 5 |
11 | B | 5 |
12 | B | 5 |
The second query also works in PostgreSQL.