0

I am attempting to run an SQL query to pull in all most recent records based off of last modified date.

Here is my query and it giving me an error of Automation failed due to a system error. Any help would be appreciated.

SELECT DISTINCT
  CreatedById,
  LastModifiedById,
  Organization__c,
  Description__c, 
  MAX([LastModifiedDate]) as [LastModifiedDate],
  IsDeleted,
  Registration_Open_Date__c,
  Registration_Close_Date__c,
  Registration_Type__c,
  Sports__c
FROM
  ent.Program__c_Salesforce 
GROUP BY
  CreatedById,
  LastModifiedById,
  Description__c,
  LastModifiedDate,
  Organization__c,
  IsDeleted,
  Registration_Open_Date__c,
  Registration_Close_Date__c,
  Registration_Type__c,
  Sports__c
5
  • 1
    Should never use DISTINCT with GROUP BY - I would delete DISTINCT from your query. I highly recommend just never using DISTINCT honestly, but that is just my opinion. I also noticed there is no conditional (WHERE/HAVING/etc) so there is no filtering of the data you are receiving. Is this intentional? If you provide a bit more detail into exactly what end results you want, I can better help optimize your query. Aug 14 '19 at 20:13
  • I included no conditional on purpose. What I am hoping to with this query is for it to pull into a new table the most recent (based on lastmodifieddate field). A unique record is based on the Organization__c field Aug 14 '19 at 20:37
  • The way you set it up is that basically everything in it is a new record. View it as everythign in Group By is a primary key in the results of the query. You will need to adjust this if your pkey in the DE is only the Organization___c field. Aug 14 '19 at 21:19
  • Thanks for the help on this. I ended up making all fields in the group by a primary field and switched it to a Select instead of a Distinct. I am still getting an error when running it. The error I am receiving is: Automation failed due to system error. Any idea why? Aug 15 '19 at 13:12
  • Check out Adam's blog for some good info on debugging that error: (sprignaturemoves.com/troubleshooting-queries-sfmc) Aug 15 '19 at 13:29
0

This could work, I've assumed the organization__c field is what you want to appear once and would like to pick the newest lastmodified date for that particular record. The data is split on organization__c and ordered by last modified date and then the newest/most recent row of each organization__c is retrieved.

SELECT 
  x.CreatedById,
  x.LastModifiedById,
  x.Organization__c,
  x.Description__c, 
  x.[LastModifiedDate],
  x.IsDeleted,
  x.Registration_Open_Date__c,
  x.Registration_Close_Date__c,
  x.Registration_Type__c,
  x.Sports__c
FROM
(
SELECT 
  CreatedById,
  LastModifiedById,
  Organization__c,
  Description__c, 
  [LastModifiedDate],
  IsDeleted,
  Registration_Open_Date__c,
  Registration_Close_Date__c,
  Registration_Type__c,
  Sports__c,
  row_number() over(partition by Organization__c order by [LastModifiedDate] DESC) rowNum
FROM
  ent.Program__c_Salesforce 
)x
where x.rowNum = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.