My SQL query is saying my order by is invalid

Question

I have a SQL query that is giving me this error.

Column INSTR.JOB_HDR.JOB_START_DTTM is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

But... I am using an aggregate, here is my query

SELECT [JOB_NAME]
       , [JOB_DESCR]
       , [INFA_FOLDER_NAME]
       , [WORKFLOW_NAME]
       , [LOAD_GROUP]
       , MAX(JOB_START_DTTM) AS 'JOB START'
       , MAX(JOB_END_DTTM) AS 'JOB END'
       , [JOB_STATUS]
FROM [INSTR].[JOB] JB
    INNER JOIN [INSTR].[JOB_HDR] JOB_HDR
        ON JB.JOB_ID = JOB_HDR.JOB_ID
WHERE (JB.JOB_NAME LIKE 'EDW%1D00%')
      AND
      (
          (JOB_STATUS = 'COMPLETE')
          OR (JOB_STATUS = 'RUNNING')
      )
GROUP BY JOB_NAME
         , JOB_DESCR
         , INFA_FOLDER_NAME
         , WORKFLOW_NAME
         , LOAD_GROUP
         , JOB_STATUS
ORDER BY JOB_HDR.JOB_START_DTTM DESC
         , JOB_HDR.JOB_END_DTTM DESC;

Funny enough, just to see, I put those columns in the group by and I get results. But the results are giving me multiple records, when I just want the highest value for the job start and end times which is why I used max.

In case it helps, this is the format of the record for job start and end.

2023-02-08 16:12:09.

What is causing the order by clause to not know that I'm using the max function?? Why is the max function not working?

I'm doing this as a test run. I work in a baby IT JOB that allows you to learn SQL while working. And then you do a "Qual book" checkout. My signatory gave me the hint to use an aggregate function to those. I suggested max and they more less confirmed without giving me the answer, cause I'm supposed to write this query on my own.

But also, he mentioned I would use a case statement for the job status (which confuses me because my where clause filters what I want already) how would a case statement? I'm not as familiar with CASE as I'd like to be and all the free resources don't seem to be making it click for me.

Thanks for any help! I mostly just really want to know the why of problem, why is my aggregate function not working....

The results are to pull those columns in the select, using aforementioned join for two tables.

The results need to show job status as "running" or "complete" with the latest run time (job start, end time). This should produce 1 unique record for each "job name" with %edw%1d00% cause I want only the latest run time and it's status.

Hope this clarifies.

Answer 1

Try this:

SELECT [JOB_NAME]
       , [JOB_DESCR]
       , [INFA_FOLDER_NAME]
       , [WORKFLOW_NAME]
       , [LOAD_GROUP]
       , MAX(JOB_START_DTTM) AS 'JOB START'
       , MAX(JOB_END_DTTM) AS 'JOB END'
       , [JOB_STATUS]
FROM [INSTR].[JOB] JB
    INNER JOIN [INSTR].[JOB_HDR] JOB_HDR
        ON JB.JOB_ID = JOB_HDR.JOB_ID
WHERE (JB.JOB_NAME LIKE 'EDW%1D00%')
      AND
      (
          (JOB_STATUS = 'COMPLETE')
          OR (JOB_STATUS = 'RUNNING')
      )
GROUP BY [JOB_NAME]
         , [JOB_DESCR]
         , [INFA_FOLDER_NAME]
         , [WORKFLOW_NAME]
         , [LOAD_GROUP]
         , [JOB_STATUS]
ORDER BY MAX(JOB_HDR.JOB_START_DTTM) DESC
         , MAX(JOB_HDR.JOB_END_DTTM) DESC;

The issue is that your order by was trying to order by things that aren't in the group by, or being aggregated, like the error mentions. The way to fix that in this case is to aggregate them in the order by