For recent versions of SQL-Server that support CTEs (and not the obsolete 2000):
Using a CTE and copying a trick of @Paul White in this answer (to get TOP in the recursive part of the CTE): How to recursively find gaps where 90 days passed, between rows
WITH CTE AS
(
-- Anchor:
-- Start with the earliest date in the table
SELECT TOP (1)
T.id, T.update_time, T.price
FROM tableX AS T
ORDER BY
T.update_time
UNION ALL
-- Recursive part
SELECT
SQ1.id, SQ1.update_time, SQ1.price
FROM
(
-- Recursively find the earliest date that is
-- more than 5 minutes after the "current" date
-- and set the new date as "current".
-- ROW_NUMBER + rn = 1 is a trick to get
-- TOP in the recursive part of the CTE
SELECT
T.id, T.update_time, T.price,
rn = ROW_NUMBER() OVER (
ORDER BY T.update_time)
FROM CTE
JOIN tableX AS T
ON T.update_time >= DATEADD(minute, 5, CTE.update_time)
) AS SQ1
WHERE
SQ1.rn = 1
)
SELECT id, update_time, price
FROM CTE ;
A cursor solution, that works in older versions (I don't have a 2000 instance to test and the following will probably need adjustments). Modified from another answer, in the same question How to recursively find gaps where 90 days passed, between rows:
(first, some needed tables and variables):
-- a table to hold the results
DECLARE @cd TABLE
( id INT NOT NULL, -- adjust types
update_time DATETIME PRIMARY KEY, -- according to
price DECIMAL(10,2) NOT NULL -- your columns
);
-- some variables
DECLARE
@id INT,
@update_time DATETIME,
@price DECIMAL(10,2),
@diff INT,
@previous_update_time DATETIME = '1900-01-01 00:00:00' ;
The actual cursor:
-- declare the cursor
DECLARE c CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT id, update_time, price
FROM tableX
ORDER BY update_time ;
-- using the cursor to fill the @cd table
OPEN c ;
FETCH NEXT FROM c INTO @id, @update_time, @price ;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @diff = DATEDIFF(minute, @previous_update_time, @update_time) ;
IF @diff >= 5
BEGIN
INSERT @cd (id, update_time, price)
VALUES (@id, @update_time, @price) ;
SET @previous_update_time = @update_time ;
END ;
FETCH NEXT FROM c INTO @id, @update_time, @price ;
END
CLOSE c;
DEALLOCATE c;
And getting the results:
-- get the results
SELECT id, update_time, price
FROM @cd
ORDER BY update_time ;
Tested at SQLfiddle (in 2008 version).
