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 recor

Solution 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) 
FROMTableGROUPBY 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

TransDateTransAmt201401014000

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.

Solution 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 
GROUPBY"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"; 

Solution 3:

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

Solution 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
GROUPBY"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"