+ Start a Discussion
Chirag MehtaChirag Mehta 

SOQL : How to select only first record from a group

Sort of confused on how to find top most item from a group in SOQL.

 

Below requirement is around Chatter objects.

 

User status update fires user trigger as it updates status field on user. It dosen't fire FeedItem trigger. However, internally corresponding FeedItem record is also inserted with Type='UserStatus'

 

So If i need to get FeedItemId for the UserStatus update, then the only way possible to get feeditemid for corresponding user status update is to query and get it. But as we are building a bulk trigger, so we would need to group feeditem table by userid(ie parentid), sort by createddate and just pick first one. However, in SOQL I'm having hard time to write a query which gives me top item from every group.


So I've currently made code to fetch all user statuses and then create a map with most recent status's feeditemid per user (But this will not work if there are 200 user status update and each user already having 100s of status update. Any advice?)

 

Map<Id,Id> userFeedItemId = new Map<Id,Id>();    	
		for(FeedItem f : [Select Id,ParentId from FeedItem where Type='UserStatus' and ParentID IN :userIDs order by CreatedDate desc,Id desc]) {
            if(!userFeedItemId.containsKey(f.ParentId)){
            	userFeedItemId.put(f.ParentId,f.Id);	
            }
        }