SQL group numbers that are 'close' together using a threshold value

Question

Consider the table:

id  value
1   2
2   4
3   6
4   9
5   10
6   12
7   19
8   20
9   22

I want to group them by a threshold value so that I can find values that are 'close' together. To do this I want another column that groups these numbers together. For this example use 2 as the threshold. The result should be like this. It does not matter what is used as the group label, just as long as it makes it easy to query later.

id  value   group_label
1   2       A
2   4       A
3   6       A
4   9       B
5   10      B
6   12      B
7   19      C
8   20      C
9   22      C

Answer 1

I couldn't get the version using lag() to work but here's a mysql query using variables

select id, value,
(case 
    when (value - @value) > 2
    then @groupLabel := @groupLabel + 1
    else @groupLabel
end) groupLabel, @value := value
from data cross join (
    select @value := -1, @groupLabel := 0
) t1
order by value

SQLFiddle

Update

Here's a query using lag

select t1.id, t1.value, count(t2.id)
from data t1 left join (
    select id, value,
    case when 
    (value - lag(value) over (order by value)) > 2
    then 1 else 0
    end groupLabel
    from data
) t2 on t2.groupLabel = 1
and t2.id <= t1.id
group by t1.id, t1.value
order by t1.value

SQLFiddle