CODEWITHSUNDEEP
sqldatetimemariadbgroupwise-maximum
Lorenzo De Francesco
Lorenzo De Francesco

Reputation: 651

SQL - Select the most close values to date in a table

Hi everyone i have a table called measure made up like this

id                                  |date               |value|type
05f643f4-9df9-4b29-b347-7e7627a12568|2020-03-15 22:00:00| 87.5|WEIGHT
3d2489cc-1c3b-40c4-8163-dd5d89281ce8|2020-04-20 22:00:00| 51.6|WEIGHT
610b3de3-ad28-4fc1-8f28-595e6464f58a|2020-04-19 22:00:00| 80  |ONERM_DEADWEIGHT
610b3de3-ad28-4fc1-8f28-595e6464f58c|2020-04-05 22:00:00| 79  |ONERM_SQUAT
610b3de3-ad28-4fc1-8f28-595e6464f58d|2020-04-01 22:00:00| 78.5|ONERM_BENCHPRESS
610b3de3-ad28-4fc1-8f28-595e6464f58e|2020-04-15 22:00:00| 81  |ONERM_DEADWEIGHT
6147803f-ee10-499e-9990-814d9562527a|2020-04-16 22:00:00| 77.2|WEIGHT
6ca210c3-0667-40e9-9d1b-d8bae3e43d9b|2020-04-19 22:00:00| 106 |ONERM_BENCHPRESS
76dd309d-b80d-4bad-b6a6-bf2b0d62adb9|2020-02-01 22:00:00| 120 |ONERM_SQUAT
774e74ac-40da-4232-be10-98ca56050d52|2020-03-01 22:00:00| 106 |ONERM_BENCHPRESS

And I want to select all the lowest values such as

id                                  |date               |value|type
05f643f4-9df9-4b29-b347-7e7627a12568|2020-03-15 22:00:00| 87.5|WEIGHT
610b3de3-ad28-4fc1-8f28-595e6464f58e|2020-04-15 22:00:00| 81  |ONERM_DEADWEIGHT
76dd309d-b80d-4bad-b6a6-bf2b0d62adb9|2020-02-01 22:00:00| 120 |ONERM_SQUAT
774e74ac-40da-4232-be10-98ca56050d52|2020-03-01 22:00:00| 106 |ONERM_BENCHPRESS

I can do it by code but i prefer to use SQL for performance reason so I tried this query:

SELECT m.value AS value, m.type AS type, 
MIN(m.date) AS date 
FROM measure m 
GROUP BY m.date, m.type, m.value

but the result is not good, I see too many results and are also somehow duplcated, how can i do?

EDIT Thank you so mutch for the help, the final ugly query is


SELECT `startValues`.`type`, `startValues`.`value` AS `start`, `startValues`.`date` AS `startDate`, `endValues`.`value` AS `end`, `endValues`.`date` AS `endDate`
FROM (
    SELECT cte.* FROM 
    (SELECT *, row_number()  OVER(PARTITION BY `type` ORDER BY `date`) AS rwn FROM measure AS rwn 
                WHERE `createdById`='076e0e51-cd29-4451-bf99-f145c4498c1c' 
                AND `date` >= '2020-01-01 00:00:00.000'
                AND `date` <= '2020-05-01 00:00:00.000'
    )  AS cte
    WHERE cte.rwn = 1
) AS startValues
JOIN (
    SELECT cte.* FROM 
    (SELECT *, row_number()  OVER(PARTITION BY `type` ORDER BY `date` DESC) AS rwn FROM measure AS rwn 
                WHERE `createdById`='076e0e51-cd29-4451-bf99-f145c4498c1c' 
                AND `date` >= '2020-01-01 00:00:00.000'
                AND `date` <= '2020-05-01 00:00:00.000'
    )  AS cte
    WHERE cte.rwn = 1
) AS endValues ON `startValues`.`type` = `endValues`.`type`

so that I can have a table like that

type             |start|startDate          |end  |endDate
WEIGHT           |87.5 |2020-02-15 22:00:00| 77.2|2020-04-19 22:00:00
ONERM_DEADWEIGHT |78.5 |2020-04-01 22:00:00| 80  |2020-04-19 22:00:00
ONERM_SQUAT      |55   |2020-04-01 22:00:00| 60  |2020-04-19 22:00:00
ONERM_BENCHPRESS |67   |2020-04-01 22:00:00| 75  |2020-04-19 22:00:00

If you want feel free to optimize my query

Upvotes: 0

Views: 144

Answers (2)

Gamingdevil
Gamingdevil

Reputation: 386

So, to get this straight, you want the specific value for the lowest date per type? Or do you want the lowest value per date, per type?

For the former, you would have to use partition over ordering by the date and selecting the value like that, like Sean said in his answer.

For the latter, just do this

SELECT m.date AS date, m.type AS type, MIN(m.value) AS value
FROM measure m 
GROUP BY m.date, m.type

You don't need to group by value as you will be selecting it and you don't need to select the min(date) as it's already being grouped on.

Upvotes: -1

Sean
Sean

Reputation: 1474

You can use the ROW_NUMBER function as a window function with OVER. The number the rows in the partition and sort by date

with cte as(
select * , row_number() over (partition by type order by date) as rwn
  from measure)

select *
  from cte 
 where rwn = 1

Upvotes: 2

Related Questions