asked
Find the count of a group of consecutive records
I have a table with an id and a flag in it. I need to get a count of every block of records where the flag = 1, 500 or more times in a row. So, if records of id 500 to 1001 all have a flag of 1, i need to get the first record, last record and the count. so the output would be 500 1001 501.. the first recordid, last recordid, and the count. This a type of gap island setup, I just cant get the syntax down. Id is a sequential number but stored as a varchar.
There is something similar posted here: [
http://blog.jooq.org/2015/11/07/how-to-find-the-longest-consecutive-series-of-events-in-sql/][1] This does it, although it may not be the prettiest way: SELECT MIN(id) [Start] , MAX(id) [End] , COUNT(id) [Count] FROM ( SELECT * , ( id - RowNum ) [Grp] FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY MEMNO ) [RowNum] , id , flag FROM [TableName] ) X WHERE flag = 1 ) Y GROUP BY Grp ORDER BY MIN(id) [1]:
http://blog.jooq.org/2015/11/07/how-to-find-the-longest-consecutive-series-of-events-in-sql/
18 People are following this question.
