SQL Server concurrent 5 timestamps in a columns

I would like to get an SQL Query that would help me to fetch the times in a row whichever lies with in 5 minutes and the count of times noticed should greater than or equal to 5?

For Example:

  • Handling large databases
  • Converting SQL CASE WHEN statement into C#
  • Large Data Set Concatenate Values into Query
  • I am unable to use THROW SQL Server 2008 R2
  • Entity framework code first migrations, sql user permissions?
  • SQL Replication Setup - Almost There
  • Column A
    17:22:23
    17:44:31
    17:25:36
    17:24:11
    17:47:39
    17:26:22
    17:22:44
    

    My results should fetch the below values only?

    17:22:23
    17:25:36
    17:24:11
    17:26:22
    17:22:44
    

    2 Solutions collect form web for “SQL Server concurrent 5 timestamps in a columns”

    the exact ask is to fetch something that lies 5 mins between each
    other and has repeated more than 5 times

    This should do it

    WITH T1
         AS (SELECT *,
                    KeepPreceding = IIF(DATEDIFF(MINUTE,  LAG(ColumnA, 4) OVER (ORDER BY ColumnA), ColumnA) <= +5, 1, 0),
                    KeepFollowing = IIF(DATEDIFF(MINUTE, LEAD(ColumnA, 4) OVER (ORDER BY ColumnA), ColumnA) >= -5, 1, 0)
             FROM   YourTable),
         T2
         AS (SELECT *,
                    /*If at least one of the 4 following rows or this one has the KeepPreceding flag then preserve this row*/
                    MAX(KeepPreceding) OVER (ORDER BY ColumnA ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING) AS KeptForPreceding,
                    /*If at least one of the 4 preceding rows or this one has the KeepFollowing flag then preserve this row*/
                    MAX(KeepFollowing) OVER (ORDER BY ColumnA ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS KeptForFollowing
             FROM   T1)
    SELECT ColumnA
    FROM   T2
    WHERE  1 IN ( KeptForPreceding, KeptForFollowing )
    ORDER  BY ColumnA; 
    

    You can use lag and lead as below:

    SELECT [ColumnA]
    FROM   (SELECT [ColumnA],
                   PrevVal = LAG([ColumnA]) OVER (ORDER BY [ColumnA]),
                   NextVal = LEAD([ColumnA]) OVER (ORDER BY [ColumnA])
            FROM   YourTable) a
    WHERE  DATEDIFF(MINUTE, PrevVal, [ColumnA]) <= 5
            OR DATEDIFF(MINUTE, [ColumnA], NextVal) <= 5; 
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.