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

user_10595773 0
Viewed 94

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.

zoho subquery aggregate-functions group-by sql
3 Answers

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
Dems_533410

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
user_13533240

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.

user_13533240
Related