Sorting and grouping a join result?

Question

I have the following query

SELECT DISTINCT XCS_TASK.WORKFLOW_ID, 


XCS_TASK.COMPLETED_BY, 
XCS_WORKFLOW.OBJECT_KEY, 
XCS_WORKFLOW.OBJECT_TYPE_ID, 
XCS_WORKFLOW.END_DATE_TIME, 
XCS_WORKFLOW.START_DATE_TIME 

FROM `XCS_TASK` 

inner JOIN XCS_WORKFLOW ON 
XCS_TASK.WORKFLOW_ID = XCS_WORKFLOW.WORKFLOW_ID 
WHERE TASK_TYPE_ID = 124 


GROUP BY XCS_WORKFLOW.OBJECT_KEY
ORDER BY XCS_WORKFLOW.START_DATE_TIME DESC

The problem is that I want to get the latest record for that OBJECT_KEY. I know above query is wrong because it groups by and then sorts the result of it. I looked in using the MAX(DATE) function but I couldn't get it to work in this scenario. Any help or pointers would be appreciated.

score 1 · Accepted Answer · answered Jan 07 '19 at 17:47

You could try joining the aggregated result for OBJECT_KEY and max date (eg: start_date_time)

SELECT  
  XCS_TASK.WORKFLOW_ID, 
  XCS_TASK.COMPLETED_BY, 
  XCS_WORKFLOW.OBJECT_KEY, 
  XCS_WORKFLOW.OBJECT_TYPE_ID, 
  XCS_WORKFLOW.END_DATE_TIME, 
  XCS_WORKFLOW.START_DATE_TIME 

FROM `XCS_TASK` 
INNER JOIN XCS_WORKFLOW ON XCS_TASK.WORKFLOW_ID = XCS_WORKFLOW.WORKFLOW_ID 
INNER JOIN  (

   SELECT
    XCS_WORKFLOW.OBJECT_KEY, 
    MAX( XCS_WORKFLOW.START_DATE_TIME ) max_date
  FROM XCS_WORKFLOW 
  GROUP BY OBJECT_KEY
) t ON t.OBJECT_KEY =  XCS_WORKFLOW.OBJECT_KEY 
  AND  XCS_WORKFLOW.OBJECT_KEY = t.max_date
WHERE TASK_TYPE_ID = 124

This worked. I just had to change your second to last line to be AND XCS_WORKFLOW.START_DATE_TIME = t.max_date. But that gives me what I want. Thank you. — polaris, Jan 07 '19 at 18:18

Sorting and grouping a join result?

1 Answers1