SQl语句？

[csover8]

数据表结构是这样：

id typeid datetime
366 189 2014-07-17 00:00:00.000
366 189 2014-07-16 00:00:00.000
366 189 2014-07-15 00:00:00.000
366 191 2014-07-14 10:29:00.000
367 190 2014-06-10 17:39:00.000

想通过sql语句，获取到typeid为不同的前6条数据，按datetime进行排序的，怎么写sql语句？谢谢。

[wangjia1991]

select top 6 typeid ,max(datetime) from table group by typeid

[以专业开发人员为伍]

http://database.9sssd.com/mssql/art/969

[zhangchi_net]

引用 3 楼 wangjia1991 的回复:

select top 6 typeid ,max(datetime) from table group by typeid

这个阔以

[xiesiren]

select * from ( select *,ROW_NUMBER()over(PARTITION by typeid order by datetime) rownum from [table])t where t.rownum=1 order by datetime

[天空丶蒋]

根据 typeid分组， DateTime排序就OK了。

[江南小鱼]

select top 6 t1.*
from table t1,(select typeid,max(datetime) datetime from table group by typeid) t2
where t1.typeid = t2.typeid and t1.datetime = t2.datetime

[csover8]

楼上的，这个方法不对哦。

[csover8]

谢楼上的，我试试下。。

[csover8]

具体的Sql呢？

[ajianchina]

var query = db.table.OrderBy(p => p.datetime).GroupBy(p => p.typeid).Select(p => p.First()).Take(6);

生成的sql如下，你参考一下：

SELECT TOP (6) [t3].[test], [t3].[id], [t3].[typeid], [t3].[datetime]
FROM (
    SELECT [t0].[typeid]
    FROM [dbo].[table] AS [t0]
    GROUP BY [t0].[typeid]
    ) AS [t1]
OUTER APPLY (
    SELECT TOP (1) 1 AS [test], [t2].[id], [t2].[typeid], [t2].[datetime]
    FROM [dbo].[table] AS [t2]
    WHERE [t1].[typeid] = [t2].[typeid]
    ORDER BY [t2].[datetime]
    ) AS [t3]
ORDER BY [t3].[datetime]

[爱_逆光]

这个好复杂，学习一下#9的