/**************************************************************************************************** */ /***************************** Finding the records which repeated more than X of times in continuously */ /**************************************************************************************************** */ /***************************************************************************************************** */ /*** writen by: Ariely Ronen ************************************************************************* */ /*** Web Site: https://ariely.info/ ****************************************************************** */ /*** Blog RSS: https://ariely.info/Blog/tabid/83/rssid/2/ ******************************************** */ /***************************************************************************************************** */ /* This blog is not discussed the issue of optimizing !!! In this blog I show the thought steps from the question to the solution, analyzing the results in each step and moving the next step until the final result. Next, after we reach an appropriate query, it is time to optimize and try to get a better result. */ -- ����� ����� ����� ��� �� ��� �� �� ����� ������ �����. ���� ���� ����� ��� �� ������� ������� -- ��� ��� �� ���� ����� ��� ������ ����� �� ���� ��� ���� ������ -- ���� ������ �� �������� ����� ����� �� ������ ������ ������ �������� ��������� ���� ������� ���� -- Select First "Log entery" row from Log Table: --- https://ariely.info/dnn/Blog/tabid/83/EntryId/68/Select-First-Log-entery-row-from-Log-Table.aspx -- ����� ���� ����� ���� �� ������ ������� ��� ����� ������ ���� ����� ���� �� ������ ������� ��� ����� ������ ������ ��� ������ ��� ������ �� /***************************************************************************************************** */ USE QQ GO /***************************************************************************************************** */ -- Determine the number of times the data should repeated in continuously declare @NumberOfConsecutive int = 2 /********************************************************************************************* DDL+DML */ declare @Tbl TABLE(Id INT IDENTITY,Name CHAR,Status TINYINT) INSERT INTO @Tbl(Name,Status) SELECT 'a',0 UNION ALL SELECT 'a',0 UNION ALL SELECT 'b',1 UNION ALL SELECT 'a',0 UNION ALL SELECT 'a',0 UNION ALL SELECT 'b',0 UNION ALL SELECT 'a',0 UNION ALL SELECT 'b',0 UNION ALL SELECT 'b',0 UNION ALL SELECT 'b',1 UNION ALL SELECT 'c',0 UNION ALL SELECT 'c',0 UNION ALL SELECT 'c',1 UNION ALL SELECT 'b',1 INSERT INTO @Tbl(Name,Status) SELECT 'k',0 UNION ALL SELECT 'k',0 UNION ALL SELECT 'k',0 UNION ALL SELECT 'k',0 UNION ALL SELECT 'k',1 UNION ALL SELECT 'k',0 UNION ALL SELECT 'k',1 UNION ALL SELECT 'k',1 UNION ALL SELECT 'k',0 UNION ALL SELECT 'k',0 UNION ALL SELECT 'k',1 UNION ALL SELECT 'k',1 /******************************************************************************************** */ select * from @Tbl /* -- To verify our results we select all the data in the table and mark the records that meet our requirements, -- in the case of searching 2 consecutive rows, and in the case of searching 3 consecutive 1 a 0 2 a 0 <== 2 3 b 1 4 a 0 5 a 0 <== 2 6 b 0 7 a 0 8 b 0 9 b 0 <== 2 10 b 1 11 c 0 12 c 0 <== 2 13 c 1 14 b 1 15 k 0 16 k 0 <== 2 17 k 0 <== 3 18 k 0 19 k 1 20 k 0 21 k 1 22 k 1 <== 2 23 k 0 24 k 0 <== 2 25 k 1 26 k 1 <== 2 */ /******************************************************************************************** */ select ROW_NUMBER() over (order by [id]) - ROW_NUMBER() over (partition by [Name],[Status] order by [id]) RN ,ROW_NUMBER() over (order by [id]) , ROW_NUMBER() over (partition by [Name],[Status] order by [id]) , * from @Tbl order by [id] /******************************************************************************************** */ select RANK() over (partition by RN,[Name],[Status] order by [id]) MyRank,* from ( select ROW_NUMBER() over (order by [id]) - ROW_NUMBER() over (partition by [Name],[Status] order by [id]) RN , * from @Tbl ) T order by [id] /******************************************************************************************** ----- */ /******************************************************************************************** Final */ /******************************************************************************************** ----- */ select [MyRank],[Id],[Name],[Status] from ( select RANK() over (partition by RN,[Name],[Status] order by [id]) MyRank,* from ( select ROW_NUMBER() over (order by [id]) - ROW_NUMBER() over (partition by [Name],[Status] order by [id]) RN , * from @Tbl ) T ) T where MyRank = @NumberOfConsecutive order by [id] -- These results obtained in the case of the Search 2 identical consecutiverecords, which we received as expected /* 2 2 a 0 2 5 a 0 2 9 b 0 2 12 c 0 2 16 k 0 2 22 k 1 2 24 k 0 2 26 k 1 */ -- These results obtained in the case of the Search 3 identical consecutiverecords, which we received as expected /* 17 k 0 */ /******************************************************************************************** what more? */ -- we use this to get the max Number Of Consecutive rows -- we can get all Consecutive rows in begger Consecutive then X -- �� ��� ������ ������ �� ������� ������� �� �� ���� ��� ����� ����� �� �� ������� ��� ��� ������� ���� �� �� ����� -- �� �� �� ����� �� ������� ������� ����� ���� �� ������ ��� �� ��� �� ��� ����� -- ����� -- order by [id] -- ���� ���� ��� ���� -- order by [Name],[Id] select [MyRank],[Id],[Name],[Status] from ( select RANK() over (partition by RN,[Name],[Status] order by [Name],[id]) MyRank,* from ( select ROW_NUMBER() over (order by [Name],[Id]) - ROW_NUMBER() over (partition by [Name],[Status] order by [Name],[Id]) RN , * from @Tbl ) T ) T where MyRank = @NumberOfConsecutive order by Id /******************************************************************************************** Have fun */ -- I hope you enjoyed the reading -- have fun and i will see you in the next blog, -- Ronen /******************************************************************************************** Close script */ GO