Get multiple max values sorting date time-CodePudding

Here is my sample data.
DateAndTime column measured per 10 seconds.
Date range is 6/30 ~ 8/31.
[DateAndTime], [TagName] is varchar(50).

DateAndTime             TagName        DataValue
2022-06-30 14:15:40    BW004_GD-4-16   99
2022-06-30 14:15:50    BW004_GD-4-16   25
2022-06-30 14:16:00    BW004_GD-4-16   99
2022-06-30 14:16:10    BW004_GD-4-16   50
2022-06-30 14:16:20    BW004_GD-4-16   99
2022-06-30 14:16:30    BW004_GD-4-16   99
.
.
.
2022-06-30 14:15:40    BW004_GD-4-17   50
2022-06-30 14:15:50    BW004_GD-4-17   40
2022-06-30 14:16:00    BW004_GD-4-17   25
.
.
.
2022-06-30 18:20:00    BW004_GD-4-17   50
2022-06-30 18:20:10    BW004_GD-4-17   50
2022-06-30 18:20:20    BW004_GD-4-17   10
.
.
.
2022-06-30 14:15:40    BW004_GD-4-18   30
2022-06-30 14:15:50    BW004_GD-4-18   40  
2022-06-30 14:16:00    BW004_GD-4-18   100
.
.
.

Here is problem.

DateAndtime is CharField, not date field.
maximum datavalue is duplicated.

I want to extract the maximum and earliest datetime of each tag by day of the week sorted by DateAndTime.

Result example:

DateAndTime            TagName         MaxValue  
2022-06-30 14:15:40    BW004_GD-4-16   99
2022-06-30 14:15:40    BW004_GD-4-17   50
2022-06-30 14:16:00    BW004_GD-4-18   100
.
.
.

SELECT LEFT([DateAndTime], 10) ,
    [TagName]
    , MAX([DataValue]) AS MaxValue
FROM [RTDB].[dbo].[Env_AI]
GROUP BY LEFT([DateAndTime], 10), [TagName]
ORDER BY [TagName]

This SQL works but does not include time.

Please help me. Thank you.

CodePudding user response：

Use dense_rank() to find the rows with maximum DataValue (partition) by Date and TagName

select *
from (
    select *,
        rn = dense_rank() over (
            partition by left(DateAndTime, 10), 
            TagName 
            order by DataValue desc
        )
    from RTDB.dbo.Env_AI
) t
where rn = 1

CodePudding user response：

Try thw following code:

SELECT  [DateAndTime]
    , [TagName]
    , [DataValue]
FROM [MyTable] m
ORDER BY m.DataValue, m.DateAndTime

SELECT CAST([DateAndTime] as DATEtime) DateAndTime
    , [TagName]
    , [DataValue]
FROM [MyTable] m
ORDER BY m.DataValue, CAST([DateAndTime] as DATEtime)