如何查询每天的第一条记录？

[youzhj]

表tb，中有字段dt(datetime),f1（int），每天有很多数据，现在要查询某月每天的第一条记录，该如何写SQL语句？

[--小F--]

select
 *
from
 tb t
where
 f1=(select max(f1) from tb where dt=t.dt)
and
 month(dt)='xx'

[-狙击手-]

select *
from tb b
where not exists(select 1 from tb where datediff(d,b.dt,dt) = 0 and dt> a.dt)

[youzhj]

[Quote=引用 2 楼 happyflystone 的回复:]
select *
from tb b
where not exists(select 1 from tb where datediff(d,b.dt,dt) = 0 and dt> a.dt)
[/Quote]
是a.dt还是b.dt啊？运行不正确啊

[-狙击手-]

不好意思　，打错了

呵呵，b

[-狙击手-]

select *
from tb b
where not exists(select 1 from tb where datediff(d,b.dt,dt) = 0 and dt < b.dt)

[东那个升]

select * from (
select *,ROW_NUMBER() over(partition by convert(char(10),dt,120) order by dt ) as rn
from tb ) a 
where rn=1

[youzhj]

[Quote=引用 5 楼 happyflystone 的回复:]
SQL code
select *
from tb b
where not exists(select 1 from tb where datediff(d,b.dt,dt) = 0 and dt < b.dt)
[/Quote]我把你这个句子改了一下

select a.Rdt,a.kc_ep,a.Kid from KC_EP a,tb_order b
where not exists(select 1 from KC_EP where datediff(dd,a.Rdt,Rdt)=0 and Rdt<a.Rdt)
order by a.Rdt,b.Sid

可是运行起来太慢了，是不是 因为我用了联合查询或者表中数据过大的原因啊？有没有办法可以优化一下？
语句中表tb_order是另外做的一个排序表，以便按照某字段的特定顺序来排序查询。

[youzhj]

人都走了啊？俺在等会看看

[milkman_nuaa]

declare @t as datetime
set @t=getdate() -- datetime input

select top 1 * from #tb
where datediff(mm, @t, dt) = 0
order by dt