Mike A Mike A - 6 months ago 22
SQL Question

How to count consecutive row where one column have a specific value in SQL Server?

I have a data set that looks like this

gId mId
226 88825
226 88825
226 88825
226 88825
226 88825
226 88825
226 88832
226 88832
226 88832
226 88832
226 88863
226 88863
226 88863
226 88863
226 88863
227 89080
227 89080
227 89080
227 89148
227 89148
227 89148
227 89197
227 89197
227 89197
227 89148
227 89197
227 89197
227 89197
227 89197
227 89148
227 89197
227 89197
227 89197
227 89197
227 89148
227 89197
227 89197
227 89197
229 89267
229 89318
229 89322
231 90257
231 90340
231 90350
247 94318
247 94318
249 94642
249 94642
249 94642
249 94400
249 94642
249 94642
249 94642
249 94642
249 94642
249 94642
249 94400
249 94400
249 94400
249 94400


I need to be able to get a list of the unique
gId
column where the
mId
column contains the same value in 5 or more consecutive rows. So the above data set will return something like this

gId mId
226 88825
226 88863
249 94642


One important thing to mention that I can't change the order of this list so I have to count it top to bottom to be able to check for consecutive rows.

My table looks like this

CREATE TABLE t (
gId int NOT NULL,
mId int NOT NULL
);

Answer

If you are using a version of SQL Server that supports common table expressions, this should do the trick:

WITH AtLeastFive AS (
    SELECT  gId, mId, ROW_NUMBER() OVER (PARTITION BY gId, mId ORDER BY gId, mId) AS RowNum
    FROM    t
)
SELECT * FROM AtLeastFive WHERE RowNum =5