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!

You should include the errors it's throwing. – Andy Lindeman Jan 21 '11 at 19:41 — Andy Lindeman, Jan 21 '11 at 19:41

Michael Papile · Accepted Answer · 2013-02-19T16:31:26.143

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

score 19 · Answer 2 · answered Nov 25 '11 at 14:00

19

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

answered Nov 25 '11 at 14:00

Mayur Rustagi

191
1
2

6

I wouldn't say it's an overkill considering [this quote from the manual](http://docs.mongodb.org/manual/reference/operator/aggregation/limit/) *"When a $sort immediately precedes a $limit in the pipeline, the $sort operation only maintains the top n results as it progresses, where n is the specified limit"* – aioobe May 11 '14 at 18:34

score 10 · Answer 3 · edited Jun 16 '13 at 07:05

10

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

edited Jun 16 '13 at 07:05

thefourtheye

233,700
52
457
497

answered May 24 '13 at 18:55

user2418693

101
1
2

score 5 · Answer 4 · answered Jun 05 '13 at 22:30

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

score 3 · Answer 5 · answered May 23 '13 at 20:13

3

Use aggregate():

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

answered May 23 '13 at 20:13

Vincent

16,086
18
67
73

score 3 · Answer 6 · edited May 05 '15 at 08:45

3

It will work as per your requirement.

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

edited May 05 '15 at 08:45

Alex

37,502
51
204
332

answered Mar 30 '15 at 11:30

Shariq Ansari

3,941
1
27
30

3

What's the point of this answer? Repeating the accepted answer? – Dan Dascalescu Oct 07 '18 at 00:48

score 0 · Answer 7 · answered Nov 03 '14 at 16:23

0

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

answered Nov 03 '14 at 16:23

Manish Kapoor

488
3
16

score 0 · Answer 8 · answered Jan 21 '11 at 20:32

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

Consider to iterate with MapReduce !!! This why MongoDB is cool, else use flat files ;-) — Thomas Decaux, Jun 25 '12 at 18:18

score 0 · Answer 9 · answered Sep 20 '11 at 15:36

0

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

answered Sep 20 '11 at 15:36

briangonzalez

1,606
16
21

Wouldn't find_one() be better than using limit()? `result = my_collection.find_one({}, :sort => ['_id', :desc])` – Anurag Oct 11 '12 at 21:20

Getting the highest value of a column in MongoDB

9 Answers9

Linked

Related