Find the rank of a sorted query

2,194 views
Skip to first unread message

Dave Fooks

unread,
Apr 4, 2011, 12:09:41 PM4/4/11
to mongodb-user
Hi,

Does mongodb support the ability to find the rank of a query?

For example suppose I have a scoreboard collection with users and
scores (with an index on both) how would I find a specific users rank
efficiently?
Something like:

var cursor = db.scoreboard.find({'user':'dave'}).sort('score':-1);
var userScore = cursor.next();
var rank = userScore.rank();

Thanks,

David Fooks

Robert Stam

unread,
Apr 4, 2011, 12:56:29 PM4/4/11
to mongodb-user
Here's one way you could to it with two queries.

Here's some sample data:

> db.scoreboard.find()
{ "_id" : ObjectId("4d99f71450f0ae2165669ea9"), "user" : "dave",
"score" : 4 }
{ "_id" : ObjectId("4d99f71b50f0ae2165669eaa"), "user" : "steve",
"score" : 5 }
{ "_id" : ObjectId("4d99f72350f0ae2165669eab"), "user" : "tom",
"score" : 3 }
>

First, find the score of the user "dave":

> db.scoreboard.find({ user : "dave" }, { score : 1 })
{ "_id" : ObjectId("4d99f71450f0ae2165669ea9"), "score" : 4 }

Then, count how many users have a higher score:

> db.scoreboard.find({ score : { $gt : 4 }}).count()
1

Since there is 1 higher score, dave's rank is 2 (just add 1 to the
count of higher scores to get the rank).

Dave Fooks

unread,
Apr 5, 2011, 7:20:50 AM4/5/11
to mongodb-user
Nice I like it. But is it not possible to do it with only one query?

Thanks,

David Fooks

Andreas Jung

unread,
Apr 5, 2011, 7:27:23 AM4/5/11
to mongod...@googlegroups.com
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

MongoDB just has no notion of "ranking" - whatever "ranking" means
in this context.

- -aj


- --
ZOPYX Limited | zopyx group
Charlottenstr. 37/1 | The full-service network for Zope & Plone
D-72070 T�bingen | Produce & Publish
www.zopyx.com | www.produce-and-publish.com
- ------------------------------------------------------------------------
E-Publishing, Python, Zope & Plone development, Consulting


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQGUBAEBAgAGBQJNmvybAAoJEADcfz7u4AZj0EELv3v8IADRGB3IsbqDs7GXe66S
2lnzpE6znh8hpKcqtMJYZYfy2e8mC6syuB9zkBV/OHHV91e3szCbast/NQNdbtZE
qtqT3amiyxL47a5Mhr0BT4b7B1P2PNzm2FH4rosID1r6zBog6lJVg3vg/Qy72wUX
o8rUA17ZPSENc9OBZohpP6HUlwWkxIOj6sUzxSM8162T2FH+jMRuYzVxoyFvP+i8
uJT7Yg2ZBsy84/vW6r0mo5RXMYvhosxK5upRoPMbE6T83NeDDz9WbJRvTuqDsAG2
jGa8Dtz0ltblfl5U/BqIaucpV4Ni/0eSrN8wksXylgK6gcW72JMIJUyMnD4RJyGk
etvtPHLqY5yLcVCs+B5J8WV7JBTExt9Sd14U5E7cF6sTn8sJ1hNM2B178NYVqITX
nCPC/gN9/1DqgY+gSyri8a1jkb8Db10v6JMWlb4vOU0KEP0IdSy73yjZDx9kTIl6
/90EL0kIWhn5QPVygd9Ika6OgnHGc1k=
=1CIm
-----END PGP SIGNATURE-----

lists.vcf

David Fooks

unread,
Apr 27, 2011, 12:34:26 PM4/27/11
to mongodb-user
Hi, I've tested your code Robert

db.scoreboard.find({ score : { $gt : 4 }}).count()

but it is very slow since it cant use the b-tree structure to count
the queries.
This seems a bit ridiculous. I would have thought that the rank of a
query would be a feature that should be supported by a database
system.
You would need a custom index to do it (that keeps a count of the
number of descendants of each node in the index's tree).
> D-72070 T�bingen        | Produce & Publishwww.zopyx.com          |www.produce-and-publish.com
> - ------------------------------------------------------------------------
> E-Publishing, Python, Zope & Plone development, Consulting
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.11 (Darwin)
> Comment: Using GnuPG with Mozilla -http://enigmail.mozdev.org/
>
> iQGUBAEBAgAGBQJNmvybAAoJEADcfz7u4AZj0EELv3v8IADRGB3IsbqDs7GXe66S
> 2lnzpE6znh8hpKcqtMJYZYfy2e8mC6syuB9zkBV/OHHV91e3szCbast/NQNdbtZE
> qtqT3amiyxL47a5Mhr0BT4b7B1P2PNzm2FH4rosID1r6zBog6lJVg3vg/Qy72wUX
> o8rUA17ZPSENc9OBZohpP6HUlwWkxIOj6sUzxSM8162T2FH+jMRuYzVxoyFvP+i8
> uJT7Yg2ZBsy84/vW6r0mo5RXMYvhosxK5upRoPMbE6T83NeDDz9WbJRvTuqDsAG2
> jGa8Dtz0ltblfl5U/BqIaucpV4Ni/0eSrN8wksXylgK6gcW72JMIJUyMnD4RJyGk
> etvtPHLqY5yLcVCs+B5J8WV7JBTExt9Sd14U5E7cF6sTn8sJ1hNM2B178NYVqITX
> nCPC/gN9/1DqgY+gSyri8a1jkb8Db10v6JMWlb4vOU0KEP0IdSy73yjZDx9kTIl6
> /90EL0kIWhn5QPVygd9Ika6OgnHGc1k=
> =1CIm
> -----END PGP SIGNATURE-----
>
>  lists.vcf
> < 1KViewDownload

Robert Stam

unread,
Apr 27, 2011, 1:56:52 PM4/27/11
to mongodb-user
Sorry, I don't know any faster way to do it.

I expect that if you have an index on score it can do the count by
scanning the index, which should be smaller than scanning the
collection because it is smaller.

Robert Stam

unread,
Apr 27, 2011, 2:04:53 PM4/27/11
to mongodb-user
Ooops... read: "should be *faster*" where I wrote "should be smaller".
Reply all
Reply to author
Forward
0 new messages