Show in MySQL partly distinct values

Question

My table:

id    myTimestamp             someValue
1     2020-07-18 14:00:43     11.9
2     2020-07-18 15:00:43     12.8
3     2020-07-18 16:00:43     14.2
4     2020-07-18 17:00:43     15.9
5     2020-07-19 14:10:43     10.4
6     2020-07-19 15:10:43     11.0
7     2020-07-19 16:10:43     11.4
8     2020-07-19 17:10:43     9.9
9     2020-07-20 14:20:43     10.9
10    2020-07-20 15:20:43     11.7
11    2020-07-20 16:20:43     12.9
12    2020-07-20 17:20:43     14.0

I want the distinct value like (2020-07-18 and 2020-07-19 and 2020-07-20) but also show the value of someValue.

So far I have:

SELECT DISTINCT LEFT(myTimestamp, 10) as someDate FROM minutes ORDER BY someDate

But is there a way to show the someValue? And then with (LIMIT 1) show only the highest value.

I am hoping for results like:

4     2020-07-18 17:00:43     15.9
7     2020-07-19 17:10:43     11.4
12    2020-07-20 17:20:43     14.0

My Fiddle: Fiddle

score 0 · Answer 1 · answered Jul 20 '20 at 15:33

0

SELECT DISTINCT LEFT(myTimestamp, 10) as someDate, someValue 
FROM minutes 
ORDER BY someDate
LIMIT 1

answered Jul 20 '20 at 15:33

gbalduzzi

9,356
28
58

score 0 · Answer 2 · answered Jul 20 '20 at 16:19

I don't see any way to get the correct answer using distinct/limit. I think you will need to use group by:

SELECT m.* FROM minutes m JOIN 
(SELECT LEFT(myTimestamp, 10) ts, MAX(someValue) sv
FROM minutes
GROUP BY LEFT(myTimestamp, 10)) s
ON LEFT(myTimestamp, 10) = LEFT(s.ts, 10)
AND someValue=s.sv

Show in MySQL partly distinct values

2 Answers2