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

Although oracle does own MySQL, they are still different products. — Shadow, Oct 12 '17 at 11:27
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**25 years** ago) and its use is discouraged — marc_s, Oct 12 '17 at 11:28
@Shadow Oracle owns Java too...but they can't really mess too much with it because it so widespread in open source. — Tim Biegeleisen, Oct 12 '17 at 11:29

MT0 · Accepted Answer · 2017-10-12T11:39:28.903

0

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;

edited Oct 12 '17 at 11:39

answered Oct 12 '17 at 11:34

MT0

143,790
11
59
117

yeah,it works, it gives me a time, but I look through all the data, there are some duration greater than the given time, do you know why? and why do we need the where condition here? – Yvonne Oct 12 '17 at 12:00
@Yvonne Please provide some sample data (i.e. put your DDL/DML statements into an [SQLFIDDLE](http://sqlfiddle.com/#!4) or another similar website) that will illustrate the problem. I can't guess why without seeing what is causing the problem. As to why the `WHERE` clause - you wanted the maximum so this restricts it to a single (first) row and the `ORDER BY` clause in the sub-query will put the maximum duration row first so that will be the only row returned. Try removing the `WHERE` clause and you should see it returns all the rows in descending duration order. – MT0 Oct 12 '17 at 12:06
Note: my code snippet is an answer to [the question the OP originally asked](https://stackoverflow.com/revisions/46708402/3) before they edited out some relevant context. – MT0 Oct 12 '17 at 12:23

Moudiz · Answer 2 · 2017-10-12T11:52:44.047

0

if you want alias

SELECT 
   A.DOG_ID, A.DOG_NAME, A.STORE_AREA, MAX(A.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) A group by  A.DOG_ID, A.DOG_NAME, A.STORE_AREA ;

edited Oct 12 '17 at 11:52

answered Oct 12 '17 at 11:35

Moudiz

7,211
22
78
156

This will give a `ORA-00937: not a single-group group function`. – MT0 Oct 12 '17 at 11:40
This will not find the singular dog appointment with the maximum duration instead it will find the maximum duration for each dog at each store area. – MT0 Oct 12 '17 at 11:56
@MT0 maybe you are right but my answer is to resolve the error that op had. i though thats was his question. – Moudiz Oct 12 '17 at 12:02
@Moudiz why do I need A group by…… at last? – Yvonne Oct 12 '17 at 12:10
@Yvonne because you have [aggregate function](https://learn.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql) `max()` you need to add `group by` to use the max() function or you have an error as MT0 mentioned above – Moudiz Oct 12 '17 at 12:12
This answer is valid for the recent edit to the question (which is now just a "what is wrong?" question) but doesn't match my reading of the expected answer for the [question the OP originally asked](https://stackoverflow.com/revisions/46708402/3). – MT0 Oct 12 '17 at 12:13
@MT0 with the latest edit , it only shows the error. – Moudiz Oct 12 '17 at 12:19

About the longest time by max query

2 Answers2