CODEWITHSUNDEEP
rubymongodbmongodb-ruby
roloenusa
roloenusa

Reputation: 791

Getting the highest value of a column in MongoDB

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!

Upvotes: 28

Views: 40513

Answers (9)

Shariq Ansari
Shariq Ansari

Reputation: 4641

It will work as per your requirement. 

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

Upvotes: 3

Manish Kapoor
Manish Kapoor

Reputation: 536

Following query does the same thing: db.student.find({}, {'_id':1}).sort({_id:-1}).limit(1)

For me, this produced following result: { "_id" : NumberLong(10934) }

Upvotes: 0

user2418693
user2418693

Reputation: 101

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

Upvotes: 10

jatal
jatal

Reputation: 840

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"}
    }
  } 
)

Upvotes: 5

Vincent
Vincent

Reputation: 16226

Use aggregate():

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

Upvotes: 3

Michael Papile
Michael Papile

Reputation: 6856

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

Upvotes: 48

Mayur Rustagi
Mayur Rustagi

Reputation: 191

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** }
            });

Upvotes: 19

briangonzalez
briangonzalez

Reputation: 1616

Assuming I was using the Ruby driver (I saw a mongodb-ruby tag on the bottom), I'd do something like the following if I wanted to get the maximum _id (assuming my _id is sortable). In my implementation, my _id was an integer.

result = my_collection.find({}, :sort => ['_id', :desc]).limit(1)

To get the minimum _id in the collection, just change :desc to :asc

Upvotes: 0

Simon Whitaker
Simon Whitaker

Reputation: 20576

If the column's indexed then a sort should be OK, assuming Mongo just uses the index to get an ordered collection. Otherwise it's more efficient to iterate over the collection, keeping note of the largest value seen. e.g.

max = nil
coll.find("id" => x).each do |doc| 
    if max == nil or doc['sellprice'] > max then
        max = doc['sellprice'] 
    end
end

(Apologies if my Ruby's a bit ropey, I haven't used it for a long time - but the general approach should be clear from the code.)

Upvotes: 0

Related Questions