Getting the Highest Value of a Column in Mongodb

Getting the highest value of a column in MongoDB

max() does not work the way you would expect it to in SQL for Mongo. This is perhaps going to change in future versions but as of now, max,min are to be used with indexed keys primarily internally for sharding.

see http://www.mongodb.org/display/DOCS/min+and+max+Query+Specifiers

Unfortunately for now the only way to get the max value is to sort the collection desc on that value and take the first.

transactions.find("id" => x).sort({"sellprice" => -1}).limit(1).first()

how to get the max value of a field in MongoDB

You can use a combination of limit and sort to achieve this goal.

db.collectionName.find({}).sort({"updateTime" : -1}).limit(1)

This will sort all of your fields based on update time and then only return the one largest value.

I would recommend adding an index to this field to improve performance.

Get the document with the max value of a field in MongoDB

You can put all the fields in $max operator like this... It will give you the document for the max field which is used at first in the $max object. Just I have used price here.

db.getCollection("Collection").aggregate([
  { "$match": { "product": { "$in": ["product-a"] } } },
  { "$group": {
    "_id": null,
    "maxPrice": {
      "$max": {
        "price": "$price",
        "_id": "$_id"
      }
    }
  }}
])

Mongo DB find all records with highest value depending on a key field

It does, but the approach is slightly different:

db.myCol.aggregate([
    {$sort: {value:-1}},
    {$group:{
        _id: "$user",
        doc: {$first: "$$ROOT"}
    }},
    {$replaceRoot: {newRoot: "$doc"} }
])

Getting all data with highest value in MongoDB

  db.collection.aggregate([
   {
     $sort: {
           version: -1
           }
  },
  {
   $group: {
  _id: "$scanner",
  version: {
    $first: "$version"
  },
  test: {
    $push: {
      v: "$version",
      id: "$_id",
      p: "$project"
      }
     }
    }
  },
  {
   $project: {
     items: {
      $filter: {
        input: "$test",
        as: "item",
        cond: {
        $eq: [
          "$$item.v",
          "$version"
        ]
       }
      }
     }
   }
  },
   {
    $unwind: "$items"
   },
   {
    $project: {
     scanner: "$_id",
     _id: "$items.id",
     project: "$items.p",
     version: "$items.v"
    }
   }
  ])

Explained:

1. Order(Sort) descending by version
2. group by scanner taking the first value from the list per version and pushing all values in test array so we have suitable for filter in next stage
3. Filter from test array only the elements with the max version we need
4. Unwind the test array where only max values are filtered
5. In the final $project stage rename the fields as per original names you need

playground

MongoDb Need help in finding highest value in database using aggregate

A good strategy, if you don't have an index, is to collapse all documents and get one value:

const pipeline =  [
    {
        "$project" : {
            "subTitle" : "$subject.subTitle",
            "type" : "$subject.type",
            "credit" : "$subject.credit",
            "_id" : 0
        }
    },
    {
        "$sort" : {
            "credit" : -1
        }
    },
    {
        "$limit" : 1
    }
]
db.Subject.aggregate(pipeline)

Query

• $project We want only a subset of fields
• $sort to get the highest credit first.
• $limit:1 to get only the highest.

Get maximum value for a field, but only with respect to other documents with the same value in another field

You need to use $group aggregation operator with $max accumulator.

db.collection.aggregate([
  { "$group": {
    "_id": "$device",
    "value": {
      "$max": "$value"
    }
  }},
  { "$project": { "_id": 0, "value": 1, "device": "$_id" }}
])

MongoPlayground

MongoDB retrieve data with highest value

playground

db.collection.aggregate([
  {
    "$group": { //Find max value
      "_id": "null",
      "maxV": {
        "$max": "$version"
      },
      data: { // add all records to this array
        $push: "$$ROOT"
      }
    }
  },
  {
    $project: {
      "output": {
        $filter: {//Filter the matching record from the array in stage 1
          input: "$data",
          as: "d",
          cond: {
            $eq: [
              "$$d.version",
              "$maxV"
            ]
          }
        }
      },
      "_id": 0
    }
  }
])

If you want to get rid of key output, you can two more stages as in this play

  {
    "$unwind": "$output"
  },
  {
    "$replaceRoot": {
      "newRoot": "$output"
    }
  }

---

Related Topics