the order of the rows according to the variability of the data in the rows-CodePudding

select *
(
select 1 as col1, 1 as col2
union all
select 2, 2
union all
select 3, 2
union all
select 4, 1
union all
select 5, 3
union all
select 6, 3
union all
select 7, 2
) Q

I need row numbers according to variability of the col2 with order to col1. I need check if previously row is disitnct.

col1, col2, needed
    1,  1, 1
    2,  2, 2
    3,  2, 2
    4,  1, 3
    5,  3, 4
    6,  3, 4
    7,  2, 5

CodePudding user response：

That should not be too hard with window functions:

SELECT col1, col2,
       sum(newgr) OVER (ORDER BY col1, col2)
FROM (SELECT col1, col2,
             CASE WHEN lag(col2) OVER (ORDER BY col1, col2) 
                       IS DISTINCT FROM col2 
                  THEN 1 
                  ELSE 0 
             END AS newgr
      FROM (VALUES (1, 1),
                   (2, 2),
                   (3, 2),
                   (4, 1),
                   (5, 3),
                   (6, 3),
                   (7, 2)
      ) AS q(col1,col2)
     ) AS p;

 col1 │ col2 │ sum 
══════╪══════╪═════
    1 │    1 │   1
    2 │    2 │   2
    3 │    2 │   2
    4 │    1 │   3
    5 │    3 │   4
    6 │    3 │   4
    7 │    2 │   5
(7 rows)