Clever Geek Handbook

Running number column incrementing its value by conditon

Is there a way to create a work number column that will increment its value by 1 if the value in another column has a specific value? For example, if we have

  colA 
    a 
    c 
change 
    b 
    c 
    b 
change 
    b 
    a 
    . 
    . 
    .

then I would like to have

colA          colB 
    a           1 
    c           1 
change          2 
    b           2 
    c           2 
    b           2 
change          3
    b           3 
    a           3 
    .           . 
    .           . 
    .           .
+3


source to share


2 answers


All of the solutions below assume that you have an explicit orderCriteria

one by which you order your events.

This is how you would do it with PostgreSQL 9.4:

SELECT
  colA,
  COUNT(*) FILTER (WHERE colA = 'change') OVER (ORDER BY orderCriteria) + 1 colB,
FROM my_table
ORDER BY orderCriteria

Here's how you would do it with any other RDBMS features that support windows:



(this includes CUBRID, DB2, Firebird 3, HANA, Informix, Oracle, PostgreSQL, Redshift, SQL Server, Sybase SQL Anywhere, and others)

SELECT
  colA,
  COUNT(CASE WHEN colA = 'change' THEN 1 END) OVER (ORDER BY orderCriteria) + 1 colB,
FROM my_table
ORDER BY orderCriteria

Here's how you do it with any other DBMS:

SELECT
  colA,
  (SELECT COUNT(*)
   FROM my_table t2
   WHERE t2.orderCriteria <= t1.orderCriteria
   AND t2.colA = 'change') + 1 colB
FROM my_table t1
ORDER BY orderCriteria
+4


source


You have not specified how you want to order, so I will use idColumn

:

SELECT colA,
       colB = (SELECT  COUNT(*) FROM dbo.TableName t2
               WHERE   colA = 'change'
               AND     t2.idColumn <= t.idColumn) + 1
FROM dbo.TableName t      
ORDER BY idColumn


+4


source






More articles:


All Articles