MAX() in ORACLE SQL

问题:

I have a table that stores a list of records for Maintenance tasks that have been done and the date and time that they were done. I'm trying to do a sub-query to pull out the records for each task that has the most recent date. My SQL statement is:

    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 expression"

When I remove the "GROUP BY" then I get: "ORA-00937: not a single-group group function"

1 - what exactly does that mean 2 - what is wrong with the statement?

回答1:

The columns which ever you added in the SELECT clause without an aggregate function should be in the GROUP BY clause.

To make it little bit clear:
Take this Example:

You have TransactionID, AccountID, TransactionAmount, TransactionDate in your SELECT Clause, and you need SUM(TransactionAmount) on all dates, in that case, if you add

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

Then you will get an error, why because
Assume you have a 4 transactions on 20160101 and each transactionAmount is $1000
Your result expectation will be

TransDate      TransAmt
 20140101          4000

In this case, if you bring other attributes in the SELECT clause like AccountID and TransactionID, where will they go? This is why we have to include all the attributes in GROUP Clause what ever in the SELECT clause except the one which is with the AGGREGATE function.

回答2:

I don't know MySQL at all, however, in Oracle, you need to group by all 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"; 

回答3:

You must put all columns of the SELECT in the GROUP BY

回答4:

You should set all the column in group by

 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"