Get multiple max values sorting date time

Question

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.

@Squirrel SELECT LEFT ([DateAndTime], 10) , [TagName] ,MAX([DataValue]) AS MaxValue FROM [RTDB].[dbo].[Env_AI] GROUP BY LEFT ([DateAndTime], 10), [TagName] ORDER BY [TagName] — KIHA, Aug 08 '22 at 06:02
This is an exact duplicate of your recently [deleted question](https://stackoverflow.com/questions/73272341/how-to-group-by-day-from-charfield-using-sql) - please do not do that. Improve your original question. — Dale K, Aug 08 '22 at 06:06
[Edit] your attempt into your question. And explain this result `2022-06-30 18:20:30 BW004_GD-4-16 99` comes from? "18:20:30" doesn't exist in your initial data. Nor do many of your other desired results. Also if you want to make it easy for people to assist, try providing the DDL+DML. — Dale K, Aug 08 '22 at 06:07
@DaleK Okay. "18:20:30" is my misstake. first low 2022-06-30 18:20:30 BW004_GD-4-16 99. i will try providing the DDL+DML — KIHA, Aug 08 '22 at 06:15
The first thing you need to do is make sure your desired results match your provided sample data. — Dale K, Aug 08 '22 at 06:16
sorry for the confusion, I'll add details to the question and edit sample data. — KIHA, Aug 08 '22 at 06:25

score -1 · Answer 1 · edited Aug 08 '22 at 06:13

-1

Try thw following code:

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

OR

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

edited Aug 08 '22 at 06:13

Dale K

25,246
15
42
71

answered Aug 08 '22 at 05:51

AliNajafZadeh

1,216
2
13
22

2

A good answer contains an explanation in addition to working code. – Dale K Aug 08 '22 at 06:07

score -1 · Accepted Answer · edited Aug 08 '22 at 06:40

-1

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

edited Aug 08 '22 at 06:40

Dale K

25,246
15
42
71

answered Aug 08 '22 at 06:37

Squirrel

23,507
4
34
32

Thank you so much. This code works. But my data is over 4 million and it takes time. Can I get only the earliest date each tag among the max? – KIHA Aug 08 '22 at 06:43
@KIHA that sounds like a separate question. Either way, provide sample data and desired results. Although 4m rows isn't many, so maybe you need some indexes in your database. – Dale K Aug 08 '22 at 06:45
I am currently developing a backend using django rest framework. I used django orm, but it is too slow to use. Now your query works fine, but it takes more than 3 seconds. So I changed to display only the earliest time among the maximum values. – KIHA Aug 08 '22 at 07:29
Unfortunately my main language is Python and I'm not familiar with sql yet. I need your help. – KIHA Aug 08 '22 at 07:30
@KIHA to make this run fast, you need to create a computed column `left(DateAndTime, 10)` and then create an index `(YourNewColumn, TagName, DataValue) INCLUDE (OtherColumnsHere)`. I'd also advise to use `CAST(DateAndTime AS date)` rather than `left(DateAndTime, 10)` – Charlieface Aug 08 '22 at 11:04

Get multiple max values sorting date time

2 Answers2