Zoho & SQL - How to get min value from group by and (sub)group by

Question

I have to write a query that allows to get the milestone of each project whose date is the shortest. My dataset looks like this :

Project table

    +----+-------+-----------+
    | ID | NAME  |   OTHERS  | 
    +----+-------+-----------+
    |  1 | A     |  W        | 
    |  2 | B     |  X        | 
    |  3 | C     |  Y        | 
    |  4 | D     |  Z        | 
    |... | ...   |  ...      | 
    +----+-------+-----------+

Milestone table

    +----+-------+-----------+
    | ID | NAME  | PROJECTID | 
    +----+-------+-----------+
    | 11 | A1    |  1        | 
    | 21 | B1    |  1        | 
    | 31 | C1    |  2        | 
    | 41 | D1    |  3        | 
    | 51 | E1    |  3        | 
    +----+-------+-----------+

AND Tasks table

    +----+-------+-----------+-------------+
    | ID | MILEID| PROJECTID | DATE        |
    +----+-------+-----------+-------------+
    |111 | 11    |  1        | 18/02/2022  |
    |121 | 11    |  1        | 20/03/2022  |
    |131 | 21    |  1        | 20/06/2022  |
    |141 | 21    |  1        | 01/03/2022  |
    |211 | 31    |  2        | 15/06/2021  |
    |311 | 41    |  3        | 10/05/2021  |
    |312 | 41    |  3        | 30/07/2022  |
    |321 | 51    |  3        | 05/01/2022  |
    |322 | 51    |  3        | 11/04/2022  |
    +----+-------+-----------+-------------+

Expected result is :

    +-----+-------+
    | PID | MID   |    
    +-----+-------+
    |  1  | 11    |    (because 18/02/2022 is the min date all over tasks)
    |  2  | 31    |
    |  3  | 41    |    (because 10/05/2021 is the min date all over tasks)
    +-----+-------+

As you can see there are multiples tasks per milestone and there are multiples milestones per project. All over my tests, my query return all milestones per project not the one I need. I have to say I m' working with Zoho project Analytics so it only support standard query (not CTE or else) and only support 2 levels of query. Thanks a lot for your help.

Answer 1

Does this work?

SELECT
     T.ProjectID
       ,MIN(T.MileID) AS MileID
FROM
     Tasks AS T
  JOIN
       (
       SELECT
               ProjectID
               ,MIN(Date) AS MinDate
       FROM
             Tasks
       GROUP BY
               ProjectID
     ) AS FirstDate ON FirstDate.ProjectID = T.ProjectID
       AND FirstDate.MinDate = T.Date
GROUP BY
       T.ProjectID

The subselect will get the lowest date for each project The outer select will get the lowest milestone ID for the project where the milestone date matches the lowest project date.