select num,convert(char(10),create_time,120)
from table a
where not exists (
select 1 from table where create_time = a.create_time and num < a.num
)
from table a
where not exists (
select 1 from table where create_time = a.create_time and num < a.num
)
insert @t select 1,'123','2006-1-18 11:36:52' union all
select 2,'123','2006-1-19 11:36:52' union all
select 3,'234','2006-1-19 12:36:52'
select num,convert(char(10),create_time,120) as create_time
from @t a
where not exists (
select 1 from @t
where convert(char(10),create_time,120) = convert(char(10),a.create_time,120) and a.num > num
)/*
num create_time
----------- -----------
1 2006-01-18
2 2006-01-19*/
from table1
group by convert(varchar(10),create_time,120)