我有一张桌子,类似于：
Id Name EnteredOn Percentage ````````````````````````````````````````````````````````````` 01 person1 2011-03-09 17:29:35.683 56.29 02 person1 2011-03-09 17:29:35.731 76.29 03 person1 2011-03-09 18:15:78.683 56.29 04 person1 2011-03-10 17:29:35.683 56.29 05 person1 2011-03-10 16:29:31.683 56.29 06 person1 2011-03-11 17:29:35.683 56.29
总结上表,第09天有三行,第10天有两行.
现在,我只想选择最新的行 – 每行一行.
(一行为9,一行为10,一行为11)
由于时间戳,我不能使用distinct.我不能分组并使用：
CAST(CONVERT(FLOAT,EnteredOn) AS INT)
因为当我选择EnteredOn字段时,它会抱怨它没有分组.我不能组合distinct(cast..date …),因为我无法获得正确的语法.
我如何选择 – 只有Name,EnteredOn,Percentage字段与每天不同？
提前谢谢了.
解决方法
;with cte as
(
select
*,row_number() over(partition by datediff(d,EnteredOn) order by EnteredOn desc) as rn
from YourTable
)
select *
from cte
where rn = 1
