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
Improve this question
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.
    – Gortonington
    Commented Aug 14, 2019 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
    – William Lepsche
    Commented Aug 14, 2019 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.
    – Gortonington
    Commented Aug 14, 2019 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?
    – William Lepsche
    Commented Aug 15, 2019 at 13:12
  • Check out Adam's blog for some good info on debugging that error: (sprignaturemoves.com/troubleshooting-queries-sfmc)
    – Gortonington
    Commented Aug 15, 2019 at 13:29

1 Answer 1

Reset to default
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
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .