CODEWITHSUNDEEP
mongodbmongodb-query
DraganS
DraganS

Reputation: 2699

MongoDB group and sort documents

Is it possible to sort the data based on the max. value from the group. The data:

> db.test.find({})
{"a" : 1, "d" : 10 }
{"a" : 1, "d" : 1 }
{"a" : 2, "d" : 50 } // changed, it was initially ({"a" : 2, "d" : 20 })
{"a" : 2, "d" : 2 }
{"a" : 3, "d" : 30 }
{"a" : 3, "d" : 4 }
{"a" : 1, "d" : 10 }

Result:

a: 2, d: 50 | a: 2: d: 2 | a: 3, d: 30 | a: 3, d: 4 | a: 1, d: 10 | a: 1, d: 10 | a: 1, d 1

SQL equivalent:

create table test(a integer, d integer);
insert into test 
select 3,  30 union all
select 3,  4 union all
select 2,  20 union all
select 2,  2 union all
select 1, 10 union all
select 1,  10 union all
select 1, 1;

select a, d, max(d) over (partition by a) as max_in_group
from test
order by 3 desc, 2 desc;

or

select *
from test t
inner join 
(
select a, max ( d ) as max_d
from test
group by a
)X on t.a = x.a
order by max_d desc, d desc

Thanks.

Upvotes: 0

Views: 71

Answers (1)

evilive
evilive

Reputation: 1879

consider using aggregation framework:

db.coll.aggregate(
  {$sort: {d: -1}},
  {$group: {_id: "$a", d: {$push: "$d"}, max: {$max: "$d"}}},
  {$sort: {max: -1}},
  {$unwind: "$d"},
  {$project: {a: "$_id", d: 1, _id: false}}
);

result: 

{ "d" : 30, "a" : 3 }
{ "d" : 4,  "a" : 3 }
{ "d" : 20, "a" : 2 }
{ "d" : 2,  "a" : 2 }
{ "d" : 10, "a" : 1 }
{ "d" : 10, "a" : 1 }
{ "d" : 1,  "a" : 1 }

P.S.: you can remove first pipeline stage {$sort: {d: -1}} if you don't care of order inside group; you'll get next result:

{ "d" : 30, "a" : 3 }
{ "d" : 4,  "a" : 3 }
{ "d" : 20, "a" : 2 }
{ "d" : 2,  "a" : 2 }
{ "d" : 10, "a" : 1 } 
{ "d" : 1,  "a" : 1 } // <--   not
{ "d" : 10, "a" : 1 } // <-- ordered

Upvotes: 1

Related Questions