SQL Grouping Integers by Range

Question

I have integer values: (199903, 199908, 201203, 201408, 201410, 201501, 201503) and I would like to group these integers by integers falling within a range of 3.

In this example the grouping would be the following:

199903 (group 1)
199908 (group 2)
201203 (group 3)
201408 (group 4)
201410 (group 4)
201501 (group 5)
201503 (group 5)

What happens if you have 201505? Is that part of group 5 or is it group 6? — zimdanen, Oct 15 '15 at 21:11

Lukasz Szozda · Accepted Answer · 2015-10-15T21:17:34.277

You can use windowed function DENSE_RANK:

LiveDemo

CREATE TABLE #mytable(val INTEGER);

INSERT INTO #mytable(val)
VALUES(199903),(199908),(201203),(201408),(201410),(201501),(201503);

SELECT 
   val,
   [group] = DENSE_RANK() OVER (ORDER BY val/3)
FROM #mytable;

Output:

╔════════╦═══════╗
║  val   ║ group ║
╠════════╬═══════╣
║ 199903 ║     1 ║
║ 199908 ║     2 ║
║ 201203 ║     3 ║
║ 201408 ║     4 ║
║ 201410 ║     4 ║
║ 201501 ║     5 ║
║ 201503 ║     5 ║
╚════════╩═══════╝

Thank you! I had to use val/4 but this works great! – decompiled Oct 16 '15 at 14:12 — decompiled, Oct 16 '15 at 14:12

score 0 · Answer 2 · answered Oct 15 '15 at 21:37

I suspect you mean sequences that differ by three or less. So, a new period starts when the difference is greater than 3. In SQL Server 2012+, you can use lag() for this. In SQL Server 2008, here is one way:

with t as (
      select t.*,
             (case when t.val - tprev.val < 3 then 0 else 1 end) as IsGroupStart
      from table t outer apply
           (select top 1 t2.val
            from table t2
            where t2.val < t.val
            order by t2.val desc
           ) tprev
     ) t
select t.val, t2.grp
from t outer apply 
     (select sum(IsGroupStart) as grp
      from t t2
      where t2.val <= t.val
     ) t2;

SQL Grouping Integers by Range

2 Answers2