Mongodb: Select the top N rows from each group

I use mongodb for my blog platform, where users can create their own blogs. All entries from all blogs are in an entries collection. The document of an entry looks like:

{ 'blog_id':xxx, 'timestamp':xxx, 'title':xxx, 'content':xxx }

As the question says, is there any way to select, say, last 3 entries for each blog?

-------------Problems Reply------------

The only way to do this in basic mongo if you can live with two things :

  • An additional field in your entry document, let's call it "age"
  • A new blog entry taking an additional update

If so, here's how you do it :

  1. Upon creating a new intro do your normal insert and then execute this update to increase the age of all posts (including the one you just inserted for this blog) :

    db.entries.update({blog_id: BLOG_ID}, {age:{$inc:1}}, false, true)

  2. When querying, use the following query which will return the most recent 3 entries for each blog :

    db.entries.find({age:{$lte:3}, timestamp:{$gte:STARTOFMONTH, $lt:ENDOFMONTH}}).sort({blog_id:1, age:1})

Note that this solution is actually concurrency safe (no entries with duplicate ages).

This answer using map reduce by drcosta from another question did the trick

In mongo, how do I use map reduce to get a group by ordered by most recent

mapper = function () {
emit(this.category, {top:[this.score]});
}

reducer = function (key, values) {
var scores = [];
values.forEach(
function (obj) {
obj.top.forEach(
function (score) {
scores[scores.length] = score;
});
});
scores.sort();
scores.reverse();
return {top:scores.slice(0, 3)};
}

function find_top_scores(categories) {
var query = [];
db.top_foos.find({_id:{$in:categories}}).forEach(
function (topscores) {
query[query.length] = {
category:topscores._id,
score:{$in:topscores.value.top}
};
});
return db.foo.find({$or:query});

It's possible with group (aggregation), but this will create a full-table scan.

Do you really need exactly 3 or can you set a limit...e.g.: max 3 posts from the last week/month?

Category:mongodb Views:1 Time:2011-06-27
Tags: mongodb

Related post

  • selecting top N rows for each group in a table 2010-10-22

    I am facing a very common issue regarding "Selecting top N rows for each group in a table". Consider a table with id, name, hair_colour, score columns. I want a resultset such that, for each hair colour, get me top 3 scorer names. To solve this i got

  • How to get TOP (1) row within each Group in sql server 2000 2010-11-23

    I have some following set of data from where I want to select Top 1 row for each PK_PatientId based on the current order PK_PatientId PK_PatientVisitId PK_VisitProcedureId DateSort ------------ ----------------- ------------------- ------------------

  • Get top 1 row of each group 2011-07-27

    I have a table which I want to get the latest entry for each group. Here's the table: DocumentStatusLogs Table |ID| DocumentID | Status | DateCreated | | 2| 1 | S1 | 7/29/2011 | | 3| 1 | S2 | 7/30/2011 | | 6| 1 | S1 | 8/02/2011 | | 1| 2 | S1 | 7/28/2

  • How to select the top 2 of each id Group in SQL server 2011-10-07

    I have the the following query and against each id im getting more then 3 action/code Can you adivse me how to get the top two actions against each id by using a select statement instead of writing a procedure SELECT id, code, date, des FROM accounts

  • How can I Select the Top and Bottom Record, Per Group? 2009-04-07

    Say, I have a table in the database (SQL Server 2008) with data similar to this (but much, much bigger): | ID | SCORE | GROUP | ----------------------- | 10 | 1 | A | | 6 | 2 | A | | 3 | 3 | A | |----|-------|-------| | 8 | 5 | B | |----|-------|----

  • Sql query for Top N rows using a group by 2011-02-23

    Hi i tried many ways to solve this but missing some thing.. I have two tables Student and Score Sid Cid Score 6 1 90 1 1 80 4 1 80 3 1 70 2 1 60 6 2 80 1 2 70 2 2 60 4 2 60 5 2 50 4 3 80 7 3 80 6 3 70 8 3 60 2 3 50 Sid Sname Sbday Ssex 1 As 1980 fema

  • SELECT TOP(N) Rows For Each GROUP 2011-10-19

    Here is my query thus far: SELECT [Id], [HotelName], [StarRating], [Description], [CheckinDate], [CheckoutDate], [Price], [ImageUrl] FROM ( SELECT TOP (6) [Id], [HotelName], [StarRating], [Description], [CheckinDate], [CheckoutDate], [Price], [ImageU

  • select the TOP N rows from a table 2012-02-05

    I am making some paging, and I need to make some query and get the result form defined slicing . for example: I need to get all "top" rows in range 20n < x < 40n etc. SELECT * FROM Reflow WHERE ReflowProcessID = somenumber ORDER BY ID DESC; and

  • including limit to select top n rows within a group by clause 2011-12-07

    I have the following 2 tables: create table1 ( SENDER int, RECEIVER int, TIME time, TYPE char(1) ); create table2 ( ID int, Y int, CONTACT int, DATE time ); I am executing the following join query: SELECT B.ID, A.RECEIVER AS Z, A.SENDER AS CONTACT, A

  • Jpql selecting the one maximum row from each group 2010-11-25

    I am relatively new to JPA and I would like to solve the following problem using jpql exclusively (note that the implementation I am using is Datanucleus): I have a table of versioned entities, and I would like to get the latest version for all entit

  • SQL Server - SELECT TOP 5 rows for each FK 2009-09-20

    I've got the following query, that looks up the TOP 5 Products matching the search. Each Product is associated with a Shop SELECT TOP 5 * FROM Products p, Shops s WHERE p.ShopId = s.ShopId AND p.ProductName LIKE '%christmas%' I need to extend this so

  • SQL Server 2008 default select top n row 2010-12-22

    In the MS SQL Management studio you can select top n row as an action in the table menu. I know how configuring the number of returned rows. I want to now if there is a way to configure to return rows in desc ordering ? --------------Solutions-------

  • How to select top N rows in a Linq GroupBy 2011-04-24

    hope you can help me out here. I have a list of Bookings where I would like to get the top 2 rows in each group of TourOperators. here's a sample of the data: List<Booking> list = new List<Booking>(); list.Add(new Booking() { BookingNo =

  • In SQL, how do you get the top N rows ordered by a certain column? 2009-04-29

    I want to select the top N rows of a table, ranked by how high the number in one of their columns is. I already have: SELECT * FROM movie ORDER BY worldwide_gross DESC; How can I get the first twenty? If it makes any difference I'm using MySQL. Cheer

  • hibernate top N rows HQL query 2010-12-12

    I am using the following code to execute an HQL query with Hibernate: String myHqlQuery = "select from MyTable where Something = ? order by SomeVal"; // Set bind values ... getHibernateTemplate().find(myHqlQuery, bindParams); Now, I want to select th

  • Select from a certain row and onwards with mysql 2011-09-21

    How can I select from a certain row and onwards? For instance, the table I want to query, pg_id pg_title pg_backdate 1 a 2012-09-18 13:32:49 2 b 2011-09-18 13:32:49 3 c 2011-06-18 13:32:49 4 d 2010-05-18 13:32:49 5 e 2009-04-18 13:32:49 6 f 2011-10-1

  • Pulled top 5 rows from multiple SQL tables, how do I display data from each row into a label, VB.NET? 2012-04-14

    This question is a follow up to an earlier question I had that was quickly resolved. The previous question can be found here: Selecting TOP 4 records from multiple SQL Server tables. Using vb.net I selected the top 4 rows from multiple SQL tables ord

  • Return top # of rows from left table while returning all rows from right joined tables 2014-03-25

    I'm trying to limit the number or orders returned but not the total of rows. I would like to select the top 15 rows from the orders table but return all related rows from the joined tables. The statement below returns a total of 15 rows total rather

  • How do I select top 10 rows in a table without sorting? 2008-11-22

    Possible Duplicate: How do I select last 5 rows in a table without sorting? I want to select the top 10 records from a table in SQL Server without arranging the table in ascending or descending order. --------------Solutions------------- if random or

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.134 (s). 11 q(s)