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

Answer

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

Advertisement

Answer