求教分类语句

col1               datetime                   col2               col3         col4
搜索 2007-01-17 899273 897433 898170
社区 2007-01-17 880617 878828 879561
....................................................
中国 2007-01-18 239731 239255 239580
美国 2007-01-18 136138 135834 136063
....................................................
生活 2007-01-19 131117 130830 131061
女人 2007-01-19 103850 103572 103794
....................................................

我想求出每天col2列排名前十位的行，多谢指教。

------解决方案--------------------
select top 10 * from table order by col4 desc
------解决方案--------------------
select * from T as tmp
where (select count(*) from T where datetime=tmp.datetime and col2> tmp.col2) <10
order by col2
------解决方案--------------------
select top 10 * from T
where convert(char(10), [datetime], 120)=convert(char(10), getdate(), 120)
order by col2 desc

------解决方案--------------------
select * from tablename a
where col1 in (
select top 10 col1
from tablename
where [datetime]=a.[datetime]
)

------解决方案--------------------
--try

select * from T as tmp
where (select count(*) from T where col1=tmp.col1 and col2> tmp.col2) <10

------解决方案--------------------
pbsh(大航海家) ( ) 信誉：99 Blog 2007-01-30 11:13:12 得分: 0


没说清，不好意思。

按col1分类，每个col1的前十位。


-----------------------------------
例子给出的可不是按col1分类，按按col1分类的语句如下：

select * from tablename a
where col2 in (
select top 10 col2
from tablename
where [datetime]=a.[datetime]
and col1=a.col1
order by col2 desc
)


上面的更正：
select * from tablename a
where col1 in (
select top 10 col1
from tablename
where [datetime]=a.[datetime]
order by col2 desc
)