function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Trisha HinojosaTrisha Hinojosa 

  SOQL: return first created record in object X for a list of users

Hello,

I have a custom object called "Matters". It's records are created by a product integration so I have to use a picklist field and user lookup field as a proxy for "created by". The picklist field is Salesperso__c and was replaced by a lookup field Salesperson__c at a later date.

I'm stuck trying to iterate through a list so I get the first matter for every user. 

This is what I was able to put togehter so far:
SELECT Id, Name, Salesperso__c, Salesperson__c, CreatedDate FROM Matter__c order by createdDate ASC limit 1

This only gives me the first matter ever made. Not the first matter for each salesperson.

How can I incorporate something like this: list <user> fmatter = [select id from matter order by createdDate ASC limit 1];
Should I? Do I have to do two separate queries, because I have 2 different data types for the salesperson field? 

New to SOQL.

Thank you! 

EDIT: I also tried the following query below, but it is not allowed: "[object Object]: Ordered field must be grouped or aggregated: CreatedDate"

SELECT Id, Name, Salesperso__c, Salesperson__c, CreatedDate FROM Matter__c 
GROUP BY Salesperson__c, Salesperso__c
ORDER BY createdDate ASC limit 1

...when I add CreatedDate to group by: "Malformed Query: 
[object Object]: Salesperson__c, Salesperso__c, createdDate ORDER BY createdDate ^ ERROR at Row:1:Column:116 field 'createdDate' can not be grouped in a query call"

I know I'm probably not understanding something elemental. I apologize for my ignorance. 
Steven NsubugaSteven Nsubuga
Try this 
 list <AggregateResult> fmatter = [SELECT MIN(CreatedDate), Id, Name, Salesperso__c, Salesperson__c
FROM Matter__c 
GROUP BY Salesperso__c, Salesperson__c, Id, Name];

 
Trisha HinojosaTrisha Hinojosa
Hey Steven,

I get this error: "The query has to start with 'FIND' or 'SELECT'."

 
Steven NsubugaSteven Nsubuga
In that case use the select portion of what I shared. SELECT min(CreatedDate), Salesperso__c, Salesperson__c, Id, Name FROM Matter__c GROUP BY Salesperso__c, Salesperson__c, Id, Name
Trisha HinojosaTrisha Hinojosa
Gave it a whirl. :/ Exceeded_ID_Limit [object Object]: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch
Steven NsubugaSteven Nsubuga
To the end of the query add the following phrase LIMIT 1000
Trisha HinojosaTrisha Hinojosa
Fixes that error, thank you. However, all of the records have NULL salesperson fields.

Tried the query below, but got a parsing error: "Unknown error parsing query"

SELECT min(CreatedDate), Salesperso__c, Salesperson__c, Id, Name 
FROM Matter__c  
GROUP BY Salesperso__c, Salesperson__c, Id, Name
HAVING Salesperso__c, Salesperson__c IS NOT NULL   
LIMIT 1000
Steven NsubugaSteven Nsubuga
SELECT min(CreatedDate), Salesperso__c, Salesperson__c, Id, Name 
FROM Matter__c  
WHERE Salesperso__c != null AND  Salesperson__c != null 
GROUP BY Salesperso__c, Salesperson__c, Id, Name
LIMIT 1000