How to use ORDER BY and GROUP BY together in the same query

Question

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

Answer 1

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);
}