Posted on 2011-09-23
Hi, We have this sql statement
SET DateIssued = a.DateIssued , RenewalDate = a.RenewalDate , ServiceLevel = a.ServiceLevel
FROM BRenewals b INNER JOIN
(SELECT DateIssued, MAX(RenewalDate) AS RenewalDate, ServiceLevel, AlarmRef
GROUP BY AlarmRef, DateIssued, ServiceLevel) a ON b.AlarmRef = a.AlarmRef
- We are trying to insert the max value into a table BRenewals where AlarmRef are equal for fields in question
- Focus is on the nested SELECT statement!
- It works perfectly when we remove all fields other than AlarmRef and RenewalDate in the select and the GROUP BY (i.e. it shows and inserts into BRenewals the MAX value)
- However, when uisng all the fields specified in SQL above, it basically returns records if two records exist (i.e. it doesn't doesn't show the MAX value only)
Can anybody help with this. It's so near, but we need to specify all the fields in the SELECT statement so the update has reference to them! Thought about using a temporary table to insert the SELECT statement into and then reference that temp tabke, but would rather avoid another temp table if we could!