Retrieve other fields with MAX values recorded

Asked 2 years ago, Updated 2 years ago, 34 views

I am using Microsoft SQL Server 2012.

For example, suppose you have a table with the following records:
The record is one month's worth of data every 10 minutes.(4032 Row)
DateTime, Value1, Value2
2018-02-0100: 10:000.30.3
2018-02-0100:20:000.30.6
2018-02-01 00:30:00 0.51.1
2018-02-0100:40:000.21
2018-02-01 00:50:00 0.41.1
2018-02-01:00:00.10.7
...
2018-02-28 24:00:00.10.7

What I want to do here is
Returns the maximum value of Value 2 every day and its time.

For example, in the above example, if the maximum value of Value 2 on 2/1 is 1.1, the query return value is
2018-02-01 00:30:00 0.51.1
I would like to be the case.
If Value2 has multiple 1.1s, it receives the value of the DateTime and Value1 fields as the first record to record the maximum value.

Here are some of the queries I tried:

SELECT FORMAT (DateTime, 'yyyyMMdd'), MAX (dblValue2) 
FROM Table A  
Group BY FORMAT (DateTime, 'yyyyMMdd')

With this, I could get the MAX value for each day, but I couldn't get the date when I recorded the MAX value.

Please let me know a good query to solve.

*I can solve this problem if I write programmable like C in WHILE below...
I would like to solve this problem with a query.

sql

2022-09-29 22:17

1 Answers

I haven't tried it yet...

SELECT TableA.DateTime, TableA.dblValue1, TableA.dblValue2
FROM (SELECT MIN (TableA.DateTime) ASD2
       FROM (SELECT FORMAT (DateTime, 'yyyyyMMdd') ASD1, MAX (dblValue2) ASV1
              FROM Table A  
              GROUP BY FORMAT (DateTime, 'yyyyyMMdd')
            ASQ1
       INNER JOIN TABLEA
       ON FORMAT (TableA.DateTime, 'yyyyyMMdd') = Q1.D1 AND TableA.dblValue2 = Q1.V1
       GROUP BY FORMAT (TableA.DateTime, 'yyyyMMdd')
     ASQ2
INNER JOIN TABLEA
ON TableA.DateTime=Q2.D2
ORDER BY TABLEA.DateTime


2022-09-29 22:17

If you have any answers or tips


© 2024 OneMinuteCode. All rights reserved.