4

I am trying to retrieve a list of LoginHistory for a bunch of users, but only want the most recent LoginHistory for each user.

I have been trying this query, but I keep getting an unknown parsing error

SELECT UserId, Status FROM LoginHistory WHERE UserId IN :userIds ORDER BY LoginTime DESC GROUP BY UserId

Is there anyway to accomplish this?

The equivalent query I am trying to construct is

for(Id userId : userIds){
    [SELECT status FROM LoginHistory WHERE UserId =:userId ORDER BY LoginTime LIMIT 1];
}

but I want to bulkify it with multiple userIds so I don't throw a too many soql queries error

  • 1
    Did you try reversing the order putting group by first? – Eric Apr 28 '16 at 21:31
  • Wont that order the UserIds and not the results then? @Eric – TemporaryFix Apr 28 '16 at 21:33
  • 1
    Um. Move the fields along with the keywords – Eric Apr 28 '16 at 21:50
6

You must put GROUP BY before ORDER BY. Take a look at SOQL SELECT Syntax:

SELECT fieldList [subquery][...]
[TYPEOF typeOfField whenExpression[...] elseExpression END][...]
FROM objectType[,...]
[USING SCOPE filterScope]
[WHERE conditionExpression]
[WITH [DATA CATEGORY] filteringExpression]
[GROUP BY {fieldGroupByList|ROLLUP (fieldSubtotalGroupByList)|CUBE (fieldSubtotalGroupByList)}
[HAVING havingConditionExpression] ]
[ORDER BY fieldOrderByList {ASC|DESC} [NULLS {FIRST|LAST}] ]
[LIMIT numberOfRowsToReturn]
[OFFSET numberOfRowsToSkip]
[FOR {VIEW | REFERENCE}[,...] ]
[ UPDATE {TRACKING|VIEWSTAT}[,...] ]

Also note that you must group all fields you query for. Correct syntax:

SELECT UserId, Status
FROM LoginHistory
WHERE UserId IN :userIds
GROUP BY UserId, Status
ORDER BY LoginTime DESC

If you want to get the most recent LoginHistory.Status for each specified user, I don't think aggregates will help. Normally query row limits aren't a huge concern, so you can probably just pull in the last 30 days or some such and post-process them.

Map<Id, String> mostRecentStatus = new Map<Id, String>();
for (LoginHistory history : [
    SELECT Status, UserId
    FROM LoginHistory
    WHERE Id IN :userIds
    AND LoginTime >= LAST_N_DAYS:30
    ORDER BY LoginTime, UserId DESC
]){
    if (!mostRecentStatus.containsKey(history.UserId))
        mostRecentStatus.put(history.UserId, history.Status);
}
  • can i use latModifiedDate in the combination of group by and order by – Satya Jan 3 '17 at 10:09
  • Your first query gets me this [object Object]: Ordered field must be grouped or aggregated: LoginTime – Caspar Harmer May 30 '18 at 18:37
  • And then it turns out you can't even aggregate LoginTime... and then it turns out that order by just plain doesn't work after a group by. AT ALL. – Caspar Harmer May 30 '18 at 18:37

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.