CODEWITHSUNDEEP
sqlsql-server
user4963104
user4963104

Reputation: 120

Find sequences from sql query

Can you help me find first and the last id in the squences where Active is true. So from this result

id  |   Active
---------------
1   |   false
2   |   false
3   |   true
4   |   true
5   |   false
6   |   false
7   |   false
8   |   true
9   |   true
10  |   false
11  |   false
12  |   true
13  |   true
14  |   true

I’ll need all continous sequences (where active is true) with start and end id

startId   |   endId
------------------------
3         |   4
8         |   9
12        |   14

Upvotes: 3

Views: 54

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

This is a typical gaps and islands problem. Use ROW_NUMBER to identify groups of sequential records having the same Active value:

SELECT MIN(id) AS startId, MAX(id) AS endId
FROM (
  SELECT id, Active,
         ROW_NUMBER() OVER (ORDER BY id) -
         ROW_NUMBER() OVER (PARTITION BY Active ORDER BY id) AS grp
  FROM mytable) AS t
WHERE Active = 'true'
GROUP BY grp

The outer query simply filters out Active = false records and groups by grp calculated field, in order to get startId and endId.

Demo here

Upvotes: 5

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You can do this with island solution:

DECLARE @t TABLE
    (
      id INT ,
      Active VARCHAR(10)
    )

INSERT  INTO @t
VALUES  ( 1, 'false' ),
        ( 2, 'false' ),
        ( 3, 'true' ),
        ( 4, 'true' ),
        ( 5, 'false' ),
        ( 6, 'false' ),
        ( 7, 'false' ),
        ( 8, 'true' ),
        ( 9, 'true' ),
        ( 10, 'false' ),
        ( 11, 'false' ),
        ( 12, 'true' ),
        ( 13, 'true' ),
        ( 14, 'true' );
WITH    cte
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( ORDER BY id )
                        - ROW_NUMBER() OVER ( PARTITION BY Active ORDER BY id ) rn
               FROM     @t
             )
    SELECT  MIN(id) AS StartID ,
            MAX(id) AS EndID
    FROM    cte
    WHERE   Active = 'true'
    GROUP BY rn

The idea behind is that you just assign some values to islands. Look here what you get in cte:

id  Active  rn
1   false   0
2   false   0

3   true    2
4   true    2

5   false   2
6   false   2
7   false   2

8   true    5
9   true    5

10  false   4
11  false   4

12  true    7
13  true    7
14  true    7

Upvotes: 6

Related Questions