Last Inserted Records According to Distinct Lookup Field

Question

I'm trying to figure out a way to get last inserted records according to distinct lookup field. Let's say I have data like this;

---

Field ----- Lookup ----- CreatedDate

Data1 ----- User1 ----- Inserted 5 min ago

Data2 ----- User2 ----- Inserted 6 min ago

Data3 ----- User2 ----- Inserted 7 min ago

Data4 ----- User1 ----- Inserted 8 min ago

Data5 ----- User3 ----- Inserted 10 min ago

---

And I have distinct Lookup field Set like {User1,User2,User3}

I want to get data that highlighted with bold. Is it possible? Because getting data ordered by CreatedDate does not return what I need.

UPDATE: I know that I can grab records ordered by CreatedDate then inside a for I can get latest values for distinct fields. But I have a large data set. Because of this I need to avoid limits. But limiting to query, let's say 1000, might not always return record for every distinct lookup field. For User200 last inserted record might be 20 days ago. Then by limiting query to 1000 I might not get the record for User200.

Answer 1

It would seem you can limit this in a couple of different ways. One is by number of records returned, another is by using TypeOf (see Working with Polymorphic Relationships & you would need to have this feature enabled in your org) as well as CreatedDate of either Yesterday, Today, or LAST_N_DAYS:n. See Date Formats and Date Literals for more.

list<User>ListofUsers = new list<User>();

[Select FieldName from ObjectName where TypeOf Lookup = 'User' && Lookup in: ListofUsers 
    && (Createddate > YESTERDAY && Createddate < Now()) Limit n];

Note: You can use DateTime with Date functions in SOQL query WHERE clauses. See Condition Expression Syntax (WHERE Clause).

Answer 2

Well after a bit of thought and help of a colleague I find the solution like this (but I did not test it extensively, yet. For now it seems to work just fine). Solution uses MAX function of soql. After finding MAX value for each created date grouping by user (or lookup field) I re-query with data in hand. This returns last inserted records.

        //getting lookup fields
        List<User> users = new List<User>();
        users = [SELECT Id FROM User];
        Set<Id> userIds = (new Map<Id,User>(users)).keySet();

        //getting max createdDate for users
        List<AggregateResult> records = [SELECT CreatedById, MAX(CreatedDate) FROM ObjectName WHERE CreatedById IN :userIds GROUP BY CreatedById];

        //put dates in a list
        Set<Datetime> cDates = new Set<Datetime>();
        For(AggregateResult record : records){
            cDates.add(Datetime.valueOf(record.get('expr0')));
        }

        //finally get last record for each user (or lookup field)
        List<ObjectName> latestRecords = [SELECT Id, SomeField__c FROM ObjectName WHERE CreatedById IN :userIds AND CreatedDate IN :cDates];