Getting the highest value of a column in MongoDB

Question

I've been for some help on getting the highest value on a column for a mongo document. I can sort it and get the top/bottom, but I'm pretty sure there is a better way to do it.

I tried the following (and different combinations):

transactions.find("id" => x).max({"sellprice" => 0})

But it keeps throwing errors. What's a good way to do it besides sorting and getting the top/bottom?

Thank you!

Solution 1

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()

Solution 2

Sorting might be overkill. You can just do a group by

db.messages.group(
           {key: { created_at:true },
            cond: { active:1 },
            reduce: function(obj,prev) { if(prev.cmax<obj.created_at) prev.cmax = obj.created_at; },
            initial: { cmax: **any one value** }
            });

Solution 3

db.collectionName.aggregate(
  {
    $group : 
    {
      _id  : "",
      last : 
      {
        $max : "$sellprice"
      }
    }
  }
)

Solution 4

Example mongodb shell code for computing aggregates.

see mongodb manual entry for group (many applications) :: http://docs.mongodb.org/manual/reference/aggregation/group/#stage._S_group

In the below, replace the $vars with your collection key and target variable.

db.activity.aggregate( 
  { $group : {
      _id:"$your_collection_key", 
      min: {$min : "$your_target_variable"}, 
      max: {$max : "$your_target_variable"}
    }
  } 
)

Solution 5

Use aggregate():

db.transactions.aggregate([
  {$match: {id: x}},
  {$sort: {sellprice:-1}},
  {$limit: 1},
  {$project: {sellprice: 1}}
]);

View more solutions