up vote 16 down vote favorite
8

Please help me to convert this select sentence to mongodb:

Select Name, Max(Value) From table1 Group By Name

I read this document: http://www.mongodb.org/display/DOCS/Aggregation#Aggregation-Group

but still dont know how to apply Max() method instead SUM() as that document.

Thank you.

share|improve this question
    
not tried, reduce: function(obj,prev) { if (prev.c < obj.c) { prev.c = obj.c; } }, – Zheng Kai May 16 '12 at 6:24
up vote 20 down vote

I have created Mongo Collection as follows.

{ "_id" : ObjectId("4fb36bfd3d1c88bfa15103b1"), "name" : "bob", "value" : 5 }
{ "_id" : ObjectId("4fb36c033d1c88bfa15103b2"), "name" : "bob", "value" : 3 }
{ "_id" : ObjectId("4fb36c063d1c88bfa15103b3"), "name" : "bob", "value" : 7 }
{ "_id" : ObjectId("4fb36c0c3d1c88bfa15103b4"), "name" : "john", "value" : 2 }
{ "_id" : ObjectId("4fb36c103d1c88bfa15103b5"), "name" : "john", "value" : 4 }
{ "_id" : ObjectId("4fb36c143d1c88bfa15103b6"), "name" : "john", "value" : 8 }
{ "_id" : ObjectId("4fb36c163d1c88bfa15103b7"), "name" : "john", "value" : 6 }

Then by using the following code I group it by their name and max(value)

db.table1.group(
    {key: {name:true},
        reduce: function(obj,prev) { 
            if (prev.maxValue < obj.value) { 
                prev.maxValue = obj.value; 
            }  
        },
    initial: { maxValue: 0 }}
);

The result is shown as 

[
    {
        "name" : "bob",
        "maxValue" : 7
    },
    {
        "name" : "john",
        "maxValue" : 8
    }
]

It is much simpler with the aggregation framework. You can get the same result with the following code by using aggregation framework.

db.table1.aggregate(
    {$group:{_id:"$name", "maxValue": {$max:"$value"}}}
);
share|improve this answer
4  
thank you so muck. – spidermanit May 16 '12 at 9:34
    
Hi parvin. is this method possible to check max of date, I mean the latest date? – spidermanit May 16 '12 at 10:48
    
Sorry for late response. Yes it is also possible to compare dates with this method. You only need to replace obj.date with obj.value – Parvin Gasimzade Jun 29 '12 at 6:37
up vote 16 down vote

Using the Aggregation Framework:

db.table1.aggregate({$group:{'_id':'$name', 'max':{$max:'$value'}}}, 
{$sort:{'max':1}}).result
share|improve this answer
up vote 1 down vote

Since MongoDB supports mapreduce below function should do. 

db.employee.insert({name:"Tejas",Value:2})
db.employee.insert({name:"Tejas",Value:3})
db.employee.insert({name:"Varma",Value:1})
db.employee.insert({name:"Varma",Value:6})

var map=function(){
var key={name:this.name};
var value={value:this.Value};
emit(key,value);
};

var reduce=function(key,values){
var max=-1;
values.forEach(function(value){
            if(max==-1){
        max=value['value'];
    }
    if(max<value['value']){
        max=value['value'];
    }
});
return {max:max};
};

db.employee.mapReduce(map,reduce,{out:{inline:1}});

share|improve this answer
    
thank you so much – spidermanit May 16 '12 at 9:34
up vote 0 down vote 
var myresult = db.table1.aggregate( [                              
                            { $group: 
                                  _id:"$Name",                                                                     
                                   value: { $max: "$Value" } 
                                 } 
                            } 
                            ]);
print(myresult)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.