CODEWITHSUNDEEP
mongodbaggregation-frameworknosql
Nodir
Nodir

Reputation: 369

MongoDB Group By query

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

Answers (1)

Derick
Derick

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

Related Questions