SELECT
	*,
	MIN(c) OVER(PARTITION BY GroupId) AS MinValue,
	MAX(c) OVER(PARTITION BY GroupId) AS MaxValue
FROM
(
	SELECT
		*,
		ROW_NUMBER() OVER (ORDER BY R)-
		ROW_NUMBER() OVER (PARTITION BY D ORDER BY R) AS GroupId
	FROM 
		YourTable
)s

DECLARE	@Sample TABLE
	(
		R INT NOT NULL,
		A INT NOT NULL,
		B DATE NOT NULL,
		C TIME(0) NOT NULL,
		D VARCHAR(10) NOT NULL,
		E INT NOT NULL
	);

INSERT	@Sample
	(
		R,
		A,
		B,
		C,
		D,
		E
	)
VALUES	(1, 3, '20120817', '19:29:25', 'mrt', 40),
	(2, 3, '20120817', '19:29:26', 'mrt', 40),
	(3, 3, '20120817', '19:29:33', 'mrt', 90),
	(4, 3, '20120817', '19:30:11', '987', 40),
	(5, 3, '20120817', '19:30:12', '987', 40),
	(6, 3, '20120817', '19:30:43', '987', 80),
	(7, 3, '20120817', '19:30:45', '987', 90),
	(8, 3, '20120817', '19:31:02', 'mrt', 40),
	(9, 3, '20120817', '19:31:10', 'mrt', 90);

-- SwePeso
;WITH cteSource(A, B, C, D, theGrp)
AS (
	SELECT	A,
		B,
		C,
		D,
		ROW_NUMBER() OVER (ORDER BY R) - ROW_NUMBER() OVER (PARTITION BY A, B, D ORDER BY R) AS theGrp
	FROM	@Sample
)
SELECT		A,
		B,
		D,
		MIN(C) AS theMin,
		MAX(C) AS theMax
FROM		cteSource
GROUP BY	A,
		B,
		D,
		theGrp
ORDER BY	A,
		B,
		MIN(C)

quote:

---

Originally posted by alberto_pompeo

i know this topic going longer, when i do longer test i realized some errors.

if value might like this
(1, 3, '20120817', '19:29:25', 'mrt', 40),
(2, 3, '20120817', '19:29:26', 'mrt', 40),
(3, 3, '20120817', '19:29:33', 'mrt', 90),
(4, 3, '20120817', '19:30:11', 'mrt', 40),
(5, 3, '20120817', '19:30:12', 'mrt', 40),
(6, 3, '20120817', '19:30:43', 'mrt', 80),
(7, 3, '20120817', '19:30:45', 'mrt', 90),
(8, 3, '20120817', '19:31:02', 'mrt', 40),
(9, 3, '20120817', '19:31:10', 'mrt', 90);

it returns one line instead of 3 lines. 'cause of PARTITION BY A, B, D. i couldnt realize how to fix it :(
i mean we should column e but how will i integrate it?

output 1 line grouped by row1-row2-row3
output 2 line grouped by row4-row5-row6-row7
output 3 line grouped by row8-row9

---

;WITH cteSource(A, B, C, D,E, theGrp)
AS (
	SELECT	A,
		B,
		C,
		D,
		E,
		ROW_NUMBER() OVER (ORDER BY R) - ROW_NUMBER() OVER (PARTITION BY A, B, D,E ORDER BY R) AS theGrp
	FROM	@Sample
)
SELECT		A,
		B,
		D,
		E,
		MIN(C) AS theMin,
		MAX(C) AS theMax
FROM		cteSource
GROUP BY	A,
		B,
		D,
		E,
		theGrp
ORDER BY	A,
		B,
		MIN(C)

;WITH cteSource(A, B, C, D, theGrp)
AS (
	SELECT	A,
		B,
		C,
		D,
		b.theGrp
	FROM
		@Sample a
		OUTER APPLY
		(
			SELECT MIN(R) AS theGrp
			FROM @Sample b
			WHERE b.R >= a.R
			AND EXISTS 
			(
				SELECT * FROM @Sample c
				WHERE c.R = b.R+1
				AND c.E < b.E
			)
		)b
)
SELECT		A,
		B,
		D,
		MIN(C) AS theMin,
		MAX(C) AS theMax
FROM		cteSource
GROUP BY	A,
		B,
		D,
		theGrp
ORDER BY	A,
		B,
		MIN(C)