MAX () in Oracle SQL

Question

I have a table that stores a list of records for ongoing maintenance tasks, as well as the date and time when they were completed. I am trying to run a subquery to pull records for every task that has the most recent date. My SQL statement:

    SELECT "ENGINEERING_COMPLIANCE"."EO" AS "EO",
       "ENGINEERING_COMPLIANCE"."AC" AS "AC",
       "ENGINEERING_COMPLIANCE"."PN" AS "PN",
       "ENGINEERING_COMPLIANCE"."PN_SN" AS "PN_SN",
       "ENGINEERING_COMPLIANCE"."HOURS_RESET" AS "HOURS_RESET",
       "ENGINEERING_COMPLIANCE"."MINUTES_RESET" AS "MINUTES_RESET",
       "ENGINEERING_COMPLIANCE"."CYCLES_RESET" AS "CYCLES_RESET",
       "ENGINEERING_COMPLIANCE"."RESET_DATE" AS "RESET_DATE",
       "ENGINEERING_COMPLIANCE"."RESET_HOUR" AS "RESET_HOUR",
       "ENGINEERING_COMPLIANCE"."RESET_MINUTE" AS "RESET_MINUTE",
       MAX ( "ENGINEERING_COMPLIANCE"."RESET_DATE" ) AS "LAST_COMP_DATE"
  FROM ENGINEERING_COMPLIANCE
GROUP BY ( "ENGINEERING_COMPLIANCE"."EO" ) ,
       ( "ENGINEERING_COMPLIANCE"."AC" ) ,
       ( "ENGINEERING_COMPLIANCE"."PN" ) ,
       ( "ENGINEERING_COMPLIANCE"."PN_SN" )

However, I keep getting the following error: "ORA-00979: not a GROUP BY clause"

When I delete "GROUP BY", I get: "ORA-00937: not a group group function"

1 - what exactly does this mean 2 - what is wrong with the expression?

Answer 1

The columns that you added to the SELECT clause without an aggregate function must be in the GROUP BY clause.

:
:

TransactionID, AccountID, TransactionAmount, TransactionDate SELECT, SUM (TransactionAmount) , ,

SELECT TransactionDate, TransactionID, AccountID, SUM(TransactionAmount) 
FROM Table 
GROUP BY TransactionDate

, , , 4 20160101, 1000

TransDate      TransAmt
 20140101          4000

, SELECT, AccountID TransactionID, ? GROUP Clause, - SELECT, , AGGREGATE.

Answer 2

 SELECT "ENGINEERING_COMPLIANCE"."EO" AS "EO",
   "ENGINEERING_COMPLIANCE"."AC" AS "AC",
   "ENGINEERING_COMPLIANCE"."PN" AS "PN",
   "ENGINEERING_COMPLIANCE"."PN_SN" AS "PN_SN",
   "ENGINEERING_COMPLIANCE"."HOURS_RESET" AS "HOURS_RESET",
   "ENGINEERING_COMPLIANCE"."MINUTES_RESET" AS "MINUTES_RESET",
   "ENGINEERING_COMPLIANCE"."CYCLES_RESET" AS "CYCLES_RESET",
   "ENGINEERING_COMPLIANCE"."RESET_DATE" AS "RESET_DATE",
   "ENGINEERING_COMPLIANCE"."RESET_HOUR" AS "RESET_HOUR",
   "ENGINEERING_COMPLIANCE"."RESET_MINUTE" AS "RESET_MINUTE",
   MAX ( "ENGINEERING_COMPLIANCE"."RESET_DATE" ) AS "LAST_COMP_DATE"
FROM ENGINEERING_COMPLIANCE
GROUP BY  "ENGINEERING_COMPLIANCE"."EO"  ,
   "ENGINEERING_COMPLIANCE"."AC"  ,
   "ENGINEERING_COMPLIANCE"."PN"  ,
   "ENGINEERING_COMPLIANCE"."PN_SN" , 
   "ENGINEERING_COMPLIANCE"."HOURS_RESET" ,
   "ENGINEERING_COMPLIANCE"."MINUTES_RESET" ,
   "ENGINEERING_COMPLIANCE"."CYCLES_RESET" ,
   "ENGINEERING_COMPLIANCE"."RESET_DATE",
   "ENGINEERING_COMPLIANCE"."RESET_HOUR" ,
   "ENGINEERING_COMPLIANCE"."RESET_MINUTE"

Answer 3

I don’t know MySQL at all, but in Oracle you need to group all the non-aggregate funtion columns listed in the select list.

Something like this should work:

 SELECT "engineering_compliance"."eo"               AS "EO", 
       "engineering_compliance"."ac"               AS "AC", 
       "engineering_compliance"."pn"               AS "PN", 
       "engineering_compliance"."pn_sn"            AS "PN_SN", 
       "engineering_compliance"."hours_reset"      AS "HOURS_RESET", 
       "engineering_compliance"."minutes_reset"    AS "MINUTES_RESET", 
       "engineering_compliance"."cycles_reset"     AS "CYCLES_RESET", 
       "engineering_compliance"."reset_date"       AS "RESET_DATE", 
       "engineering_compliance"."reset_hour"       AS "RESET_HOUR", 
       "engineering_compliance"."reset_minute"     AS "RESET_MINUTE", 
       Max ("engineering_compliance"."reset_date") AS "LAST_COMP_DATE" 
FROM   engineering_compliance 
GROUP  BY "engineering_compliance"."eo", 
          "engineering_compliance"."ac", 
          "engineering_compliance"."pn", 
          "engineering_compliance"."pn_sn", 
          "engineering_compliance"."hours_reset", 
          "engineering_compliance"."minutes_reset", 
          "engineering_compliance"."cycles_reset", 
          "engineering_compliance"."reset_date", 
          "engineering_compliance"."reset_hour", 
          "engineering_compliance"."reset_minute"; 

Answer 4

You must put all SELECT columns in GROUP BY