title: SQLITE Sum of a group by time limited to top N
body: <p>I have tried a search, seen that there is apparently a group-by-n-max tag, but the answers don't seem applicable to the problem I have.</p>
  <p>I have a large set of data, recording scores, attempts (and of course a load of other crud) against a timestamp , the timestamp is almost like a bucket in itself.
  What I currently have, is a relatively simplistic</p>
  <p>select sum(score),sum(attempts),time from records group by time order by time asc;</p>
  <p>This works well, apart from if the number of people changes per timestamp. So I need to limit the number to be consistent, say 40 to be summed within the group-by, and to make matters worse (although if the limit were achievable then order should be relatively similarly done), it would be an ordered list I would like to limit by.</p>
  <p>The timestamp is calculable by doing a select against the table, then I guess it would be possible to do a join with a limit. However it feels like there should be an easier method. Unfortunately it is not an average that I want, otherwise I could of course just add a count to the group.</p>
  <p>Edit: Yes, I should have included example input and output.
  Input table on the left, note that for times 4 and 8, there are 4 people, a,b,c,d only a and d though are in all times. So limiting for example 3 people as an example. On the right, calculation of their rank within each time, so for times 4 and 8, people c and d are not within the top 3 of the score rank.
  <a href="https://i.stack.imgur.com/uJ3Zf.png" rel="nofollow noreferrer">picture of input data and example rank calculation</a></p>
  <p>So the basic sum() group by time gives too large a result for times where there are 4 people, i.e. time 4 and 8
  <a href="https://i.stack.imgur.com/gWkY1.png" rel="nofollow noreferrer">image showing calculation of group-by, and desired output</a></p>
  <p>Input (hmmm the table renders properly in the preview)
  |-----|---------|-------|-------|
  |score| attempts|   time|   user|
  |-----|---------|-------|-------------
  |10|    4|  4|  a|
  |9| 6|  5|  a|
  |12|    7|  6|  a|
  |4| 8|  7|  a|
  |6| 9|  8|  a|
  |13|    1|  4|  b|
  |5| 3|  6|  b|
  |6| 5|  7|  b|
  |7| 7|  8|  b|
  |24|    2|  4|  c|
  |2| 5|  5|  c|
  |1| 7|  7|  c|
  |5| 6|  8|  c|
  |5| 3|  4|  d|
  |3| 4|  5|  d|
  |5| 6|  6|  d|
  |7| 2|  8|  d|
  |24|    2|  4|  c|
  |2| 5|  5|  c|
  |1| 7|  7|  c|
  |5| 6|  8|  c|
  |5| 3|  4|  d|
  |3| 4|  5|  d|
  |5| 6|  6|  d|
  |7| 2|  8|  d|
  |--|--|--|--|</p>
  <p>Desired output (see images for a better idea)</p>
  <p>|-----|---------|-------|
  |score| attempts|   time|
  |-----|---------|-----------|
  |47|    7|  4|
  |14|    15| 5|
  |22|    16| 6|
  |11|    20| 7|
  |20|    18| 8|
  |--|------|--|</p>