Need the logic to find the value of one column for the min of another column in same table in mysql

Question

i written the below code to get like this

SELECT `temp2`.`UserId` AS `UserId`,
              `temp2`.`ScheduleDate` AS `ScheduleDate`,
             `temp2`.`daystodue`
  FROM (
SELECT
      `temp1`.`UserId` AS `UserId`,
      `temp1`.`ScheduleDate` AS `ScheduleDate`,
      `temp1`.`DueDate` AS `DueDate`,
      `temp1`.`CompletedState` AS `CompletedState`,
      IF(((`temp1`.`CompletedState` = 0) AND ((`temp1`.`ScheduleDate` - CURDATE()) <= 0)), (TO_DAYS(`temp1`.`DueDate`) - TO_DAYS(CURDATE())), 0) AS `daystodue`
    FROM (SELECT
        `fd_dw`.`ComplianceFactTable`.`UserId` AS `UserId`,
        CAST(`fd_dw`.`ComplianceFactTable`.`CourseModule_ScheduleDateID` AS date) AS `ScheduleDate`,
        CAST(`fd_dw`.`ComplianceFactTable`.`CourseModule_dueDateID` AS date) AS `DueDate`,
        `fd_dw`.`ComplianceFactTable`.`CourseModuleComplete_completionstate` AS `CompletedState`
      FROM `fd_dw`.`ComplianceFactTable`
      WHERE ((`fd_dw`.`ComplianceFactTable`.`CourseModule_dueDateID` > 0)
      AND ((CAST(`fd_dw`.`ComplianceFactTable`.`CourseModule_dueDateID` AS date) - CURDATE()) > 0))) `temp1`) `temp2` 
  WHERE `temp2`.`UserId` IN (223699,223741,223780,223678,243988,380316,388737,121896,491562)

I got this output when i executed the above query

ID       Date      value
121896  2019-12-06  0
121896  2019-11-06  0
121896  2020-01-06  0
223678  2019-12-23  0
223678  2019-11-23  0
223678  2020-01-23  0
223678  2019-10-23  43
223699  2019-12-23  0
223699  2019-11-23  0
223699  2020-01-23  0
223699  2019-10-23  43
223741  2019-12-23  0
223741  2019-11-23  0
223741  2020-01-23  0
223741  2019-10-23  43
223780  2019-12-23  0
223780  2019-11-23  0
223780  2020-01-23  0
223780  2019-10-23  43
243988  2019-10-15  21
243988  2020-01-15  0
243988  2019-12-15  0
380316  2019-10-05  0
380316  2019-11-05  0
380316  2019-12-05  0
380316  2020-01-05  0
388737  2019-10-23  29
388737  2019-11-23  0
388737  2020-01-23  0
388737  2019-12-23  0
491562  2019-10-17  7
491562  2019-10-17  7
491562  2019-10-17  23
491562  2019-11-17  0
491562  2019-12-17  0
491562  2020-01-17  0
491562  2019-10-17  7
491562  2019-10-17  7

but i want something like this

121896  2019-11-06  0
223678  2019-10-23  43
223699  2019-10-23  43
223741  2019-10-23  43
223780  2019-10-23  43
243988  2019-10-15  21
380316  2019-10-05  0
388737  2019-10-23  29
491562  2019-10-17  7

Answer 1

I understand that, for each id, you want to record with the minimum date. When there are multiple records with the minimum date, you want the one with the minimm date.

In MySQL 8.0, you can use window functions:

select id, date, value
from (
    select 
        t.*,
        row_number() over(partition by id order by date, value) rn
    from mytable t
) t
where rn = 1

In earlier versions, you can use a correlated suquery for filtering:

select id, date, min(value) value
from mytable t
where t.date = (
    select t1.date from mytable t1 where t1.id = t.id order by t1.date, t1.value limit 1
)
group by id, date

Or:

select id, date, min(value) value
from mytable t
where t.date = (select min(t1.date) from mytable t1 where t1.id = t.id)
group by id, date

In this demo on DB Fiddle, all 3 queries return:

| id     | date       | value |
| ------ | ---------- | ----- |
| 121896 | 2019-11-06 | 0     |
| 223678 | 2019-10-23 | 43    |
| 223699 | 2019-10-23 | 43    |
| 223741 | 2019-10-23 | 43    |
| 223780 | 2019-10-23 | 43    |
| 243988 | 2019-10-15 | 21    |
| 380316 | 2019-10-05 | 0     |
| 388737 | 2019-10-23 | 29    |
| 491562 | 2019-10-17 | 7     |