Grouping data based on a condition Effectively I want to be able to group data based on a condition.

I have:



I want the data grouped using value effectively as a breakpoint.



I know how to do this with a cursor and some relatively simple logic.  However I'd rather use a set based solution.
use CTE &amp; ROW_NUMBER() function

declare @T TABLE
(name nvarchar(55),surname nvarchar(55), dates datetime, value decimal(18,2))

insert into @T values ('David','Example','2015-08-24 15:00:00.000',7.7)
insert into @T values ('David','Example','2015-09-10 15:00:00.000',7.7)
insert into @T values ('David','Example','2015-09-10 16:00:00.000',7.7)
insert into @T values ('David','Example','2015-09-10 16:00:00.000',7.8)
insert into @T values ('David','Example','2015-09-20 20:00:00.000',7.7)
insert into @T values ('David','Example','2015-09-20 20:00:00.000',7.7)

;with cte as
(SELECT name,surname, [Dates],[Value],ROW_NUMBER() OVER (PARTITION BY cast(dates as date) ORDER BY value desc)  AS RN
FROM @t)

select name,surname, [Dates],[Value] from cte
where RN = 1