get Distinct Values with Sorted Data

Question

I need a Query to get distinct keys with sorted on basis of score in Mongodb 1.6.5

I have records Like

{key ="SAGAR"
score =16
note ="test1"
}

{key ="VARPE"
score =17
note ="test1"
}

{key ="SAGAR"
score =16
note ="test2"
}

{key ="VARPE"
score =17
note ="test2"
}

I need a query which sorts all records on score and returns me distinct key.....

So what would the desired results be if you ran this query on this set of data (always the best thing to show when asking query questions) — James Avery, Jan 21 '11 at 14:08
@SagarVarpe - your accepted answer is no longer feasible (seems aggregation framework is only way to do this now). Perhaps consider changing your accepted answer? — UpTheCreek, May 04 '15 at 08:02
In mongo v2.2+, you can do: `db.test.distinct("key").sort();` — Jaider, Jan 04 '16 at 15:53

score 14 · Answer 1 · answered Aug 11 '14 at 09:49

14

You can use the aggregation framework to group by the element you want to be distinct (group makes it distinct). So if you wish to sort on score then get distinct keys you could do the following - sort by score, group by key and add score as arrays of elements (already sorted):

db.test.aggregate([
    { $sort : { score : -1 } },
    { $group : {_id : "$key", scores : { $push : "$score" } } }
])

This will result in distinct keys along with an array of scores which are those scores contained in the documents with duplicate keys. I'm not sure this is exactly what you're looking for and I know this is an old question but I thought this might help out someone else looking at it in the future - as an alternative way of doing this.

answered Aug 11 '14 at 09:49

Stuart

3,226
5
24
28

1

$push is the wrong operator for that purpose. Use $addToSet instead. – user3072843 Sep 26 '17 at 21:00
2

@user3072843 to my knowledge, $addToSet adds the items in a non-sorted order, whereas $push adds in the order that they arrive into the group, which is in this case the sorted order. – lauri108 Apr 06 '18 at 06:38
```[ { $group: { _id: "$rankingPoint", }, }, { $sort: { _id: -1 } }, ] – CoTg Jun 20 '23 at 07:04

score 6 · Accepted Answer · answered Jan 21 '11 at 16:22

6

There is distinct command in mongodb:

you can use distinct like this:

db.test.distinct({"key":true,"score":true,"note":true});

the same in relational database:

SELECT DISTINCT key,score,note FROM test;

And than sort result by adding following code:

.sort({score : 1}) // 1 = asc, -1 = desc

Total result will be like this:

 db.test.distinct({"key":true,"score":true,"note":true}).sort({score : 1});

answered Jan 21 '11 at 16:22

Andrew Orsich

52,935
16
139
134

25

Evidently this is no longer feasible (v3.8.0) - yields error that sort cannot be used with distinct. Can you confirm? – cyberwombat Nov 06 '13 at 03:45
11

sort can not be used with distinct – Maksym Polshcha Jan 02 '14 at 12:55
There's a feature request active, please vote for it if you have the same issue: https://jira.mongodb.org/browse/SERVER-2130 – UpTheCreek Feb 19 '14 at 11:11
1

I have MongoDB 3.0.7, and for 1 field it works well: `db.collection.distinct("date").sort({date:1})`. – lambruscoAcido Nov 09 '15 at 13:06
MongoDB 3.2.5 only wants a single string as argument to [`distinct`](https://docs.mongodb.org/manual/reference/method/db.collection.distinct/). – Cees Timmerman Apr 20 '16 at 12:44
Can confirm this works when passing 1 field, as [distinct](https://www.mongodb.com/docs/manual/reference/method/db.collection.distinct/) expects. – Akaisteph7 Oct 19 '22 at 20:38

score 3 · Answer 3 · answered Mar 30 '22 at 20:58

Using mongo shell v3.6.23

you can sort the data along with distinct values by using

db.test.distinct("key").sort();

you cant specify for which field you want to sort the result nor you can define the order.

eg: you cant use .sort({key:value}) here

the reason is that here sort() here is not the mongosh sort which is defined on cursor.

the distinct query returns javascript's Array.prototype, so you can use all instance methods on it

get Distinct Values with Sorted Data

3 Answers3

Linked