Search code examples
mysqlsql-serverjoincross-apply

Select property from max in cross apply SQL

I currently have this query

SELECT
  OC.*,
  NEW.LAST_JOB_RUN_DATE
FROM dbo.CUBES OC
CROSS APPLY (SELECT
  MAX(LAST_END_RUN_DATE) AS LAST_JOB_RUN_DATE
FROM dbo.JOBS OJ
WHERE OJ.CUBE_ID = OC.ID) NEW

Each CUBE can have multiple JOBS. I want this query to return me all the cubes, along with the last time the latest job was run. This works fine. Now i also want to add the STATUS of that job too, but i can't seem to get the query working. The query above works but doesn't return me the status associated with it, which is what i want.
Is there a way to change the above query to also return the STATUS of the job containing the MAX(LAST_END_RUN_DATE) ?

Solution 
  • SELECT
  OC.*,
  NEW.LAST_JOB_RUN_DATE,
  NEW.STATUS
FROM dbo.CUBES OC
OUTER APPLY (SELECT TOP (1) OJ.LAST_END_RUN_DATE AS LAST_JOB_RUN_DATE, OJ.STATUS
FROM dbo.JOBS OJ
WHERE OJ.CUBE_ID = OC.ID
ORDER BY OJ.LAST_END_RUN_DATE DESC ) NEW