About the longest time by max query

Question

SELECT 
    D.DOG_ID, D.DOG_NAME, S.STORE_AREA, MAX(DURATION) 
FROM 
    (SELECT 
         D.DOG_ID, D.DOG_NAME, S.STORE_AREA, SHD.START_TIME-END_TIME DURATION 
     FROM 
         SERVICE_HISTORY_DETAIL SHD, STORES S, DOGS D, SERVICE_HISTORY SH
     WHERE 
         D.DOG_ID = SH.DOG_ID 
         AND S.STORE_ID = SH.STORE_ID 
         AND SH.SERVICE_ID = SHD.SERVICE_ID);

ERROR at line 1:

ORA-00904: "S"."STORE_AREA": invalid identifier

I run the query in the bracket and max function separately, it works well, but if I add SELECT D.DOG_ID, D.DOG_NAME, S.STORE_AREA in the first line, it shows error, I don't know what wrong with that.

Thanks

Answer 1

Your query gives the error ORA-00904: "S"."STORE_AREA": invalid identifier because the table alias S is defined in the sub-query but not in the outer query.

If you remove the aliases then you will get the error ORA-00937: not a single-group group function because you have an aggregation function and several columns that are not being aggregated but you do not have a corresponding GROUP BY clause.

One solution is to not use aggregation functions and, instead, order the results in the subquery and then get the first (maximum) result:

SELECT * 
FROM   (
  SELECT D.DOG_ID,
        D.DOG_NAME,
        S.STORE_AREA,
        SHD.START_TIME-END_TIME DURATION 
  FROM   SERVICE_HISTORY_DETAIL SHD
        INNER JOIN SERVICE_HISTORY SH
        ON ( SH.SERVICE_ID = SHD.SERVICE_ID )
        INNER JOIN STORES S
        ON ( S.STORE_ID = SH.STORE_ID )
        INNER JOIN DOGS D
        ON ( D.DOG_ID = SH.DOG_ID)
  ORDER BY DURATION DESC
)
WHERE  ROWNUM = 1;