SQL Server Select last good value for each day with multiple rows for each day [duplicate]

yhived7q 于 5个月前发布在 Go

关注(0)|答案(1)|浏览(49)

This question already has answers here:

Get top 1 row of each group (19 answers)
Closed 2 days ago.

I have a datatable laid out as:

Volume	Datestamp
500	2024-01-02 00:05:28
550	2024-01-01 12:00:50
560	2024-01-01 11:40:20
580	2024-01-01 11:24:38
620	2024-01-01 00:04:37
630	2023-12-31 00:04:15
0	2023-12-31 00:04:09
640	2023-12-31 00:04:12
0	2023-12-30 00:04:09
730	2023-12-29 14:04:09

And I am trying to extract the last good value (IE no 0 values) for the last datestamp of the day. It should therefore give:

Volume	Date
500	2024-01-02
580	2024-01-01
630	2023-12-31
730	2023-12-29

I have tried the following code:

SELECT TOP (1000) [Volume]
FROM Mytable
WHERE [Datestamp] IN (
    SELECT MAX (Datestamp)
    FROM Mytable
    GROUP BY DATE(Datestamp)
)

But i get 'Date' is not a recognized built-in function name. If i comment out the group by Date(Datestamp) i just get a blank result

sql-server

来源：https://stackoverflow.com/questions/77745631/select-last-good-value-for-each-day-with-multiple-rows-for-each-day

1条答案

按热度按时间

wljmcqd81#

Exclude 0 values and find last timestamp per day using row_number :

select *
from (
    select
        volume,
        cast(datestamp as date) as date,
        row_number() over (partition by cast(datestamp as date) order by datestamp desc) as rn
    from t
    where volume > 0
) as x
where rn = 1
order by 2 desc

赞(0）回复(0）举报 5个月前

我来回答

SQL Server Select last good value for each day with multiple rows for each day [duplicate]

1条答案

相关问题

热门标签

最新问答