[SERVER-2397] Query to return every Nth value from the collection Created: 21/Jan/11  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Jason Thomas Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 4
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
Assigned Teams:
Query Optimization
Participants:

 Description   

Various relational databases have all implemented their own methods to return every nth value from the database. It doesn't seem like MongoDB has the capability at all.

My use case it storing a year's worth of data with second granularity. I want to graph an overview of the year and allow a drill down to smaller time granulations. Thus my year query would request every 10000th value (for example) while an hours worth would return every value. Both of these queries would return roughly the same amount of data so be worked with in the application.

I realize you'd incur a lot of file seek times if you had to grab data spanning multiple files but I still need the capability.



 Comments   
Comment by Mathias Stearn [ 06/Oct/11 ]

Some discussion of workarounds at http://groups.google.com/group/mongodb-user/browse_thread/thread/3d730dd50bffea1c

Comment by Jason Thomas [ 24/Jan/11 ]

It varies by DB because SQL doesn't define a good way to do this.

In Oracle:
select <item> from <table> group by <item>, rownum having mod(rownum, 100)=0

In MySQL:
SET @row=0;select <item>,@row:=@row+1 as num FROM <table> HAVING (num+1)%100=0

In Sqlite:
select <item> from <table> group by <item> having rowid % 100=0

Comment by Eliot Horowitz (Inactive) [ 23/Jan/11 ]

How would you do this in sql?

Generated at Thu Feb 08 02:59:50 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.