2

SQL Server has a handy operator called "TOP N WITH TIES", which returns

two or more rows that tie for last place in the limited results set

For example, TOP 3 WITH TIES from {10, 9, 8, 8, 2} will return {10, 9, 8, 8}.

Is there a single-query equivalent for TOP N WITH TIES for MongoDB?

I can't use two queries (with the second searching for the minimum value returned by the first) because I'm running the query in a Meteor publish function, which doesn't let you return more than one cursor from the same collection.

Community
  • 1
  • 1
Dan Dascalescu
  • 143,271
  • 52
  • 317
  • 404
  • What sort of cursor do you need? Is something like the output of `.fetch` okay? It can still be used through a slightly modified more manual publish method. This does sound possible using the aggregation framework, which does not return a cursor. – Tarang Jun 12 '15 at 15:19
  • [`fetch()`](http://docs.meteor.com/#/full/fetch) as is, loses reactivity. – Dan Dascalescu Jun 12 '15 at 15:37
  • How important is it that you be precise? Why not return 2*N and work it out on the client? – David Weldon Jun 12 '15 at 16:16
  • @DanDascalescu Not necessarily. You can use a basic match all observer query to check if new records change the results and report it to the client accordingly – Tarang Jun 12 '15 at 16:53

1 Answers1

2

You don't have to return the subquery to use it as a criterion. This is essentially the same thing you'd have to do in any other SQL engine. 

var min = Collection.find({},{limit:10, sort: {rank: -1}}).fetch()[9].rank
return Collection.find({rank: {$gte: min}})
Matt K
  • 4,813
  • 4
  • 22
  • 35
  • A simple solution that returns only one cursor, but still runs two queries. – Dan Dascalescu Jun 12 '15 at 15:16
  • this kinda thing will always be 2 queries, there's no way for any DB to know what documents share a tie without looking at all of them. Just depends whether it's got a syntactic sugary topping (sql server) or not. For example, check out the subquery in postgres: http://stackoverflow.com/questions/9629953/postgresql-equivalent-for-top-n-with-ties-limit-with-ties – Matt K Jun 12 '15 at 16:56