I have some data like this:
row id
1 1
2 36
3 37
4 38
5 50
6 51
I would like to query it to look like this:
row id group
1 1 1
2 36 2
3 37 2
4 38 2
5 50 3
6 51 3
... so that I can GROUP BY where the numbers are consecutively sequential.
Also, looping/cursoring is out of the question since I'm working with a pretty large set of data, thanks.
;WITH firstrows AS
(
SELECT id, ROW_NUMBER() OVER (ORDER BY id) groupid
FROM Table1 a
WHERE id - 1 NOT IN (SELECT b.id FROM Table1 b)
)
SELECT id,
(
SELECT MAX(b.groupid)
FROM firstrows b
WHERE b.id <= a.id
) groupid
FROM Table1 a
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With