Get consecutive values in a group identified by min and max value

Question

I'm trying to get groups of consecutive values from a table. I've been trying to find an example about this but I haven't been able to use it in my case.

This is a small part of a table I have.

    CardID      CardSerial                  CardStateID AssignUserID Denomination
    ----------- --------------------------- ----------- ------------ -------------
    2685680     214026002                   3           81           10
    2685681     214026003                   3           81           10
    2685682     214026004                   3           81           10
    2685683     214026005                   3           81           10
    2685684     214026006                   3           81           10
    2685778     214026100                   3           81           10
    2685779     214026101                   3           81           10
    2685780     214026102                   3           81           10
    2685781     214026103                   3           81           10
    2685782     214026104                   3           81           10
    2685878     214026200                   3           81           10
    2685879     214026201                   3           81           10
    2685880     214026202                   3           81           10
    2685881     214026203                   3           81           10
    2685882     214026204                   3           81           10
    2685883     214026205                   3           81           10
    2685884     214026206                   3           81           10

The results that I'm expecting are:

    min value       max value       cant     Den
    --------------- --------------- -------- ----
    214026002       214026006        5        10
    214026100       214026104        5        10
    214026200       214026206        7        10

The hard part for me is to get a group value to make a query like

select min(cardSerial), max(cardSerial), count(*), Den
from Cards
group by Den, GROUPING_VALUE_GENERATED

what is `GROUPING_VALUE_GENERATED` by the way? – John Woo Nov 14 '12 at 14:54 — John Woo, Nov 14 '12 at 14:54
What data-type is `cardSerial`? – MatBailie Nov 14 '12 at 14:54 — MatBailie, Nov 14 '12 at 14:54

MatBailie · Answer 1 · 2012-11-14T15:04:44.147

Assuming that cardSerial is an INT or other numeric data-type...

WITH
  sequenced AS 
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY Denomination ORDER BY cardSerial) AS sequence_id,
    *
  FROM
    Cards
)
SELECT
  min(cardSerial),
  max(cardSerial),
  count(*),
  Denomination
FROM
  Sequenced
GROUP BY
  Denomination,
  cardSerial - sequence_id
ORDER BY
  Denomination,
  cardSerial - sequence_id

If it's not a numeric data-type, convert it in the query to a suitably large numeric data-type to allow cardSerial - sequence_id to work.

How the cardSerial - sequence_id calculation gives something meaningful to group by...

214026002 - 01 = 214026001
214026003 - 02 = 214026001
214026004 - 03 = 214026001
214026005 - 04 = 214026001
214026006 - 05 = 214026001
214026100 - 06 = 214026094
214026101 - 07 = 214026094
214026102 - 08 = 214026094
214026103 - 09 = 214026094
214026104 - 10 = 214026094
214026200 - 11 = 214026189
214026201 - 12 = 214026189
214026202 - 13 = 214026189
214026203 - 14 = 214026189
214026204 - 15 = 214026189
214026205 - 16 = 214026189
214026206 - 17 = 214026189

Thank you very much!!! You made it posible in a very nice way!! Thx! :) by the way... CardSerial it was a int column so that logic fit very good!! — user1257292, Nov 14 '12 at 15:10

score 0 · Answer 2 · answered Jun 11 '21 at 15:39

This query is very much useful. Can we update the minimum value against the respective groups.

Example in the table one more column as "Min_CardSerial" and the value to update for this column is "214026002" for the row between 214026002 and 214026006, 214026100 is for the rows between 214026100 and 214026104 and so on.

Get consecutive values in a group identified by min and max value

2 Answers2