Reputation: 369
How I can implement on MongoDb this SQL Query
SELECT TOP 100 * FROM Tracks
WHERE ID IN (SELECT MAX(ID) FROM Tracks WHERE UserID IN ([UserIDs...]) GROUP BY UserID)
Tracks structure:
Tracks[{_id, userId, {lat, lon}, dateCreate, ...}, ...]
Thanks!
Upvotes: 1
Views: 1286
Reputation: 36794
You'd want to use the aggregation framework for this:
db.Tracks.aggregate( [
{ $match: { 'UserID': { $in: [ UserIDs ] } } },
{ $group: { _id: '$UserID', max: { $max: '$_id' } },
{ $sort: { $max: -1 } },
{ $limit: 100 }
] );
First we match against the wanted UserIDs, then we group depending on UserID and also put the maximum _id
value in the new max
field. Then we sort by max
descendently to get the highest max
numbers first and then we limit by the top 100.
Upvotes: 2