Ranking by groups of continuous equal values

Question

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

Answer 1

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.

Oracle fiddle PostgreSQL fiddle

Answer 2

This works for either RDBMS:

SELECT ord, name
     , count(step or NULL) OVER (ORDER BY ord) AS myrank
FROM  (
   SELECT ord, name, name <> lag(name) OVER (ORDER BY ord) AS step
   FROM   tbl
   ) sub;

Postgres fiddle
Oracle fiddle

Notably, count() only counts not-null values, and

true  OR null → true
false OR null → null

Counting the number of changes in the name column generates the desired group numbers.

Optimized for Postgres:

SELECT ord, name
     , count(*) FILTER (WHERE step) OVER (ORDER BY ord) + 1 AS myrank
FROM  (
   SELECT *, name <> lag(name) OVER (ORDER BY ord) AS step
   FROM   tbl
   ) sub;

Related:

• Form groups of consecutive rows with same value