sql2005一条查询语句解决方法
sql2005一条查询语句
有个表t1
t1字段:
id tid date
id是主键，tid是可重复的
select max(date) from t1 group by tid
如上语句，tid要去重，根据最大时间把id取出来，怎么办啊，谢谢各位！！！！！！
------解决思路----------------------
select id,tid,[date] from (
select *,row_number() over(partition by tid order by [date] desc ) as rn from t1
) as a where rn=1
------解决思路----------------------
SQL 2005+有效
有个表t1
t1字段:
id tid date
id是主键，tid是可重复的
select max(date) from t1 group by tid
如上语句，tid要去重，根据最大时间把id取出来，怎么办啊，谢谢各位！！！！！！
------解决思路----------------------
select id,tid,[date] from (
select *,row_number() over(partition by tid order by [date] desc ) as rn from t1
) as a where rn=1
------解决思路----------------------
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(PARTITION BY tid ORDER BY[date]DESC)RN,id,tid,[date]FROM t1
)
SELECT id,tid,[date]FROM CTE WHERE RN=1
SQL 2005+有效