Group records in ranges

Question

I have a set of 'followed' rows with the same value and I want to group them together to make a from and to. The proposed solution is usually to use ROW_NUMBER() and then group, but I find that there are cases that do not work, with an example it will be better understood:

WITH
CTE AS
    (SELECT *
     FROM (VALUES ('A', 37, ''),
                  ('A', 38, ''),
                  ('A', 39, ''),
                  ('A', 40, ''),
                  ('A', 41, ''),
                  ('A', 42, ''),
                  ('A', 43, ''),
                  ('B', 44, '=> error Grupo 43'),
                  ('B', 45, '=> error Grupo 43'),
                  ('C', 46, ''),
                  ('C', 47, ''),
                  ('B', 48, ''),
                  ('B', 49, ''),
                  ('B', 50, ''),
                  ('A', 51, '=> error Grupo 43'),
                  ('A', 52, '=> error Grupo 43'),
                  ('A', 53, '=> error Grupo 43')) DATA (LETRA, DESDE, ERROR) )
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY LETRA ORDER BY DESDE) AS rn,
       DESDE - ROW_NUMBER() OVER (PARTITION BY LETRA ORDER BY DESDE) AS Grupo
FROM CTE
ORDER BY SINCE;

Is there a way to 'group' the records correctly?

a greeting

What is your expected result set here? (P.s. `;` is a statement terminator, not a "Beginningator"). — Thom A, Jul 19 '18 at 10:21
BTW https://stackoverflow.com/questions/6938060/common-table-expression-why-semicolon — Cato, Jul 19 '18 at 10:38

score 2 · Answer 1 · answered Jul 19 '18 at 10:38

This is a gap and island problem and your calculus with DESDE - ROW_NUMBER() is correct, but you should further group by the letter instead of just this rest result.

WITH
CTE AS
    (SELECT *
     FROM (VALUES ('A', 37, ''),
                  ('A', 38, ''),
                  ('A', 39, ''),
                  ('A', 40, ''),
                  ('A', 41, ''),
                  ('A', 42, ''),
                  ('A', 43, ''),
                  ('B', 44, '=> error Grupo 43'),
                  ('B', 45, '=> error Grupo 43'),
                  ('C', 46, ''),
                  ('C', 47, ''),
                  ('B', 48, ''),
                  ('B', 49, ''),
                  ('B', 50, ''),
                  ('A', 51, '=> error Grupo 43'),
                  ('A', 52, '=> error Grupo 43'),
                  ('A', 53, '=> error Grupo 43')) DATA (LETRA, DESDE, ERROR))
,
Groups AS 
(
    SELECT
        C.*,
        GroupID = C.DESDE - ROW_NUMBER() OVER (PARTITION BY LETRA ORDER BY C.DESDE)
    FROM
        CTE AS C
)
SELECT
    C.LETRA,
    Desde = MIN(C.DESDE),
    Hasta = MAX(C.DESDE)
FROM
    Groups AS C
GROUP BY
    C.LETRA,
    C.GroupID

Result:

LETRA   Desde   Hasta
A       37      43
A       51      53
B       44      45
B       48      50
C       46      47

score 1 · Answer 2 · answered Jul 19 '18 at 10:47

Your code should work if you use aggregation:

SELECT MIN(DESDE), MAX(DESDE), ERROR
FROM (SELECT CTE.*,
             ROW_NUMBER() OVER (PARTITION BY LETRA ORDER BY DESDE) AS rn,
             DESDE - ROW_NUMBER() OVER (PARTITION BY LETRA ORDER BY DESDE) AS Grupo
      FROM CTE
     ) x
GROUP BY Grupo, ERROR;

Group records in ranges

2 Answers2