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

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.

CodePudding user response：

I know nothing about Zoho, but there are several standard approaches, the first two that come to my mind are...

Correlated Sub-Query:

SELECT
  id     AS pid,
  (
    SELECT TOP 1 task.id
      FROM task
     WHERE task.projectid = project.id
  ORDER BY task.date DESC
  )
    AS mid
FROM
  project

Window Function (ROW_NUMBER()):

SELECT
  task_sorted.*
FROM
(
  SELECT task.*,
         ROW_NUMBER() OVER (PARTITION BY task.projectid ORDER BY task.date) AS project_task_row
    FROM task
)
  AS task_sorted
WHERE
  task_sorted.project_task_row = 1

Oh, and the join approach...

SELECT
  task.*
FROM
  task
INNER JOIN
(
  SELECT projectid, MIN(date) AS date
    FROM task
GROUP BY project_id
)
  AS first_task
    ON  first_task.projectid = task.projectid
    AND first_tast.date      = task.date

NOTE: If a project has multiple tasks that share the same date...

The first two queries will arbitrarily pick one of them
The final query will return all of them

CodePudding user response：

don't know anything about ZOHO but.

SELECT 
       T.ProjectID AS [PID]
       ,T.MileID AS [MID]
FROM
       Tasks AS T 
  JOIN
       (/*Get the earliest date for each project*/
       SELECT
               T.ProjectID
              ,MIN(T.Date) AS [minDate]
       FROM
              Tasks AS T
       GROUP BY
              T.ProjectID
       ) AS MinTask ON MinTask.ProjectID = T.ProjectID
       AND MinTask.minDate = T.Date /*This gets the Task ID for the min date for the project*/

This code assumes no ties on date within a project. If you have tied dates you will need another way to disambiguate the MIN record, Best suggestion would be to use RANK() OVER() in the outer query as a filter for RANK value = 1

SELECT 
       T.ProjectID AS [PID]
       ,T.MileID AS [MID]
FROM
       Tasks AS T 
  JOIN
       (/*Get the earliest date for each project*/
       SELECT
               T.ProjectID
              ,MIN(T.Date) AS [minDate]
       FROM
              Tasks AS T
       GROUP BY
              T.ProjectID
       ) AS MinTask ON MinTask.ProjectID = T.ProjectID
       AND MinTask.minDate = T.Date /*This gets the Task ID for the min date for the project*/
WHERE
      RANK() OVER(PARTITION BY T.ProjectID,T.Date ORDER BY T.MileID) = 1