January 5, 2012

SQL ‘Group By’ + ‘Order By’ in Mongodb

Filed under: Uncategorized — Tags: , — yasi8h @ 4:50 am

As a newbie to mongodb i was a bit puzzled when i couldn’t find a equivalent to the SQL group by combined with order by in mongodb.


If you just want to go a SQL group by in mongodb you can use the group() function (refer however the results you get from group function cannot be sorted using the sort() function. This is because group function does not return a cursor (on which you can sort(), limit()…etc), but a single document.

You can’t sort the document wich is returned by group but you can sort the document in the client side. This is one way to go about doing a ‘group by + order by’. However if you don’t want to sort things in the client side you can use mapreduce.


This is probably not as simple as you would want it to be. But until mongodb supports a one liner for group by + order by function this is the only way to go about it.

You can refer the MapReduce documentation in the official site to get a better understanding about how it works. But if you just want to do a simple group by + order by equivalent in mongodb you can look at the sample bellow.

Mongodb Data Structure
gplus_profiles: id(objectId), have_user_in_circles_count (int)
SELECT COUNT(id), `have_user_in_circles_count` from `gplus_profiles`GROUP BY `have_user_in_circles_count` ORDER BY 1 DESC
m = function(){
emit(this.have_user_in_circles_count, { count : 1 });
r = function(key, values) {
var total = 0;
for ( var i=0; i<values.length; i++ )
total += values[i].count;
return { count:total };
res = db.gplus_profiles.mapReduce(m, r, { out : "myoutputt" } );

Although we have to use mapreduce for the time being, a later version of mongodb will support group by + order by like functionality natively.


Create a free website or blog at