CODEWITHSUNDEEP
sqloracle-databasegaps-and-islands
Alok
Alok

Reputation: 59

how to fetch continuous occurrence count of a column value in sql?

I have added one more id column for the order purpose, if the table is like this

+-----+-------+
| id  | cs_id |
+-----+-------+
|   1 | a     |
|   2 | b     |
|   3 | a     |
|   4 | a     |
|   5 | a     |
|   6 | b     |
|   7 | b     |
|   8 | b     |    
|   9 | b     | 
+-----+-------+

i want the continuous occurrence of cs_id order by id column 

+-----+-------+---------------------------------
| id  | cs_id |    continuous_occurrence_cs_id 
+-----+-------+---------------------------------|
|   1 | a     |    1 
|   2 | b     |    1 
|   3 | a     |    1 
|   4 | a     |    2 
|   5 | a     |    3 
|   6 | b     |    1 
|   7 | b     |    2
|   8 | b     |    3
|   9 | b     |    4 
+-----+-------+---------------------------------+

Upvotes: 0

Views: 788

Answers (1)

krokodilko
krokodilko

Reputation: 36087

First of all, in SQL by the definition data has no any order unless the ORDER BYis used.
See: Wikipedia - Order By

ORDER BY is the only way to sort the rows in the result set.
Without this clause, the relational database system may return the rows in any order.

You must provide an additional column to your table that determines the order, and can be used in ORDER BY clause, for exampleRN column in the below example:

        RN CS_ID     
---------- ----------
         1 a         
         2 b         
         3 a         
         4 a         
         5 a         
         6 b         
         7 b         
         8 b         
         9 b

For the above data you can use Common Table Expression (recursive query) to get required result, for example the below query works on Oracle database:

WITH my_query( RN, cs_id , cont ) AS (

    SELECT t.rn, t.cs_id, 1
        FROM My_table t
        WHERE rn = 1
    UNION ALL
    SELECT t.rn, t.cs_id,
         case when t.cs_id = m.cs_id
              then m.cont + 1
              else 1
         end
        FROM My_table t
        JOIN my_query m
        ON t.rn = m.rn + 1
)
select * from my_query
order by rn;

        RN CS_ID            CONT
---------- ---------- ----------
         1 a                   1
         2 b                   1
         3 a                   1
         4 a                   2
         5 a                   3
         6 b                   1
         7 b                   2
         8 b                   3
         9 b                   4

Upvotes: 3

Related Questions