仅获取 TSQL 中每天多个条目的最后一行 [英] get only last row in each day's multiple entries in TSQL

问题描述

我有一张桌子，比如:

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天有两行>.

To summarize the above table, there are three rows for day 09, and two rows for day 10.

现在，我只想每天选择最新的一行 - 单行.
(一排9，一排10，一排11)

Now, I just want to select the latest row - one single row - per day.
(one row for 9, one for 10 and the one for 11)

由于时间戳的原因，我无法使用 distinct.我不能分组和使用:

I cannot use distinct because of the timestamp. I cant group and use:

CAST(CONVERT(FLOAT, EnteredOn) AS INT)

因为当我选择 EnteredOn 字段时，它抱怨它没有分组.我无法组合 distinct(cast..date...) 因为我无法获得正确的语法.

because when I select EnteredOn field, it complaints that its not grouped. I cant combine distinct(cast..date...) because I cant get the right syntax.

我如何选择 - 仅名称、输入时间、百分比字段每天都不同?

How can I select - only Name, EnteredOn, Percentage fields with distinct to each day?

非常感谢.

推荐答案

;with cte as
(
  select
    *,
    row_number() over(partition by datediff(d, 0, EnteredOn) order by EnteredOn desc) as rn 
  from YourTable
)
select *
from cte  
where rn = 1

这篇关于仅获取 TSQL 中每天多个条目的最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！