MongoDB Query to Find Latest Record Per Group

Use MongoDB aggregation with $sort by group key and date descending, then $group by the group key picking the first record with $first, like: db.collection.aggregate([{ $sort: { groupField: 1, dateField: -1 } }, { $group: { _id: "$groupField", latestRecord: { $first: "$$ROOT" } } }]).

Examples

Input[{_id:1, category:'A', date:'2023-01-01'}, {_id:2, category:'A', date:'2023-01-05'}, {_id:3, category:'B', date:'2023-01-03'}]

Output[{_id:'A', latestRecord:{_id:2, category:'A', date:'2023-01-05'}}, {_id:'B', latestRecord:{_id:3, category:'B', date:'2023-01-03'}}]

Input[{_id:1, type:'X', timestamp:100}, {_id:2, type:'X', timestamp:90}, {_id:3, type:'Y', timestamp:110}]

Output[{_id:'X', latestRecord:{_id:1, type:'X', timestamp:100}}, {_id:'Y', latestRecord:{_id:3, type:'Y', timestamp:110}}]

Input[]

Output[]

How to Think About It

To find the latest record per group, first sort all records by the group key and the date or timestamp descending. Then group the sorted records by the group key and pick the first record in each group, which will be the latest due to sorting.

Algorithm

1

Sort all records by group key ascending and date descending

2

Group records by the group key

3

For each group, select the first record as the latest

4

Return the grouped latest records

Code

mongodb

db.collection.aggregate([
  { $sort: { category: 1, date: -1 } },
  { $group: { _id: "$category", latestRecord: { $first: "$$ROOT" } } }
])

Output

[
  {"_id": "A", "latestRecord": {"_id": 2, "category": "A", "date": "2023-01-05"}},
  {"_id": "B", "latestRecord": {"_id": 3, "category": "B", "date": "2023-01-03"}}
]

Dry Run

Let's trace the example with records grouped by 'category' and sorted by 'date' descending.

1

Sort records

Sort by category ascending and date descending: [{_id:2, category:'A', date:'2023-01-05'}, {_id:1, category:'A', date:'2023-01-01'}, {_id:3, category:'B', date:'2023-01-03'}]

2

Group and pick first

Group by category: 'A' -> first record {_id:2,...}, 'B' -> first record {_id:3,...}

category	date	picked latest _id
A	2023-01-05	2
B	2023-01-03	3

Why This Works

Step 1: Sorting ensures latest first

Sorting by the date descending puts the newest record at the top for each group.

Step 2: Grouping collects by key

Grouping by the group key gathers all records of the same group together.

Step 3: Picking first record per group

Using $first after sorting picks the latest record for each group.

Alternative Approaches

Using $group with $max and $lookup

mongodb

db.collection.aggregate([
  { $group: { _id: "$category", maxDate: { $max: "$date" } } },
  { $lookup: {
      from: "collection",
      let: { cat: "$_id", maxD: "$maxDate" },
      pipeline: [
        { $match: { $expr: { $and: [ { $eq: ["$category", "$$cat"] }, { $eq: ["$date", "$$maxD"] } ] } } }
      ],
      as: "latestRecord"
  } },
  { $unwind: "$latestRecord" }
])

This method uses $max to find the latest date per group and then $lookup to fetch the full record; it can be slower due to the join.

Using $sortByCount with $first (legacy approach)

mongodb

db.collection.aggregate([
  { $sort: { date: -1 } },
  { $group: { _id: "$category", latestRecord: { $first: "$$ROOT" } } }
])

Simpler but does not sort by group key explicitly; works if grouping key is unique or sorting by date is enough.

Complexity: O(n log n) time, O(n) space

Time Complexity

Sorting all records by group and date takes O(n log n) time, which dominates the aggregation.

Space Complexity

Aggregation stores intermediate results and groups, requiring O(n) space in worst case.

Which Approach is Fastest?

The $sort + $group with $first approach is usually faster than $group + $lookup because it avoids extra joins.

Approach	Time	Space	Best For
$sort + $group with $first	O(n log n)	O(n)	Simple and efficient for large datasets
$group + $max + $lookup	O(n) + join cost	O(n)	When you need full record but sorting is expensive
$sortByCount + $first (legacy)	O(n log n)	O(n)	Simple cases with unique keys