Limit results to x groups

Question

I'm developing a system using Trac, and I want to limit the number of "changelog" entries returned. The issue is that Trac collates these entries from multiple tables using a union, and then later combines them into single 'changesets' based on their timestamp. I wish to limit the results to the latest e.g. 3 changesets, but this requires retrieving as many rows as necessary until I've got 3 unique timestamps. Solution needs to work for SQLite/Postgres.

Trac's current SQL

Current SQL Result

Time                User  Field         oldvalue   newvalue   permanent
=======================================================================
1371806593507544    a     owner         b         c           1
1371806593507544    a     comment       2         lipsum      1
1371806593507544    a     description   foo       bar         1
1371806593324529    b     comment       hello     world       1
1371806593125677    c     priority      minor     major       1
1371806592492812    d     comment       x         y           1

Intended SQL Result (Limited to 1 timestamp e.g.)

Time                User  Field         oldvalue   newvalue   permanent
=======================================================================
1371806593507544    a     owner         b         c           1
1371806593507544    a     comment       2         lipsum      1
1371806593507544    a     description   foo       bar         1

score 1 · Accepted Answer · answered Jun 21 '13 at 15:35

As you already pointed out on your own, this cannot be resolved in SQL due to the undetermined number of results. And I think this is not even required.

You can use a slightly modified trac/ticket/templates/ticket.html Genshi template to get what you want. Change

      <div id="changelog">
        <py:for each="change in changes">

into

      <div id="changelog">
        <py:for each="change in changes[-3:]">

and place the file into <env>/templates/ restart your web-server. But watch out for changes to ticket.html, whenever you attempt to upgrade your Trac install. Every time you do that, you might need to re-apply this change on the current template of the respective version. But IMHO its still a lot faster and cleaner than to patch Trac core code.

Thanks for this. In the end I've opted to basically do this (though our templates are quite a lot different from vanilla trac). I've changed the get_changelog to be a generator function which should also speed things up. I understand that even if I was to do this with SQL then it would be a confusing statement, but I still feel like getting all the results and then only using a few won't save me that much time. — Ian Clark, Jun 24 '13 at 08:13

score 0 · Answer 2 · answered Jun 21 '13 at 10:30

0

If you want just three records (as in the "Data Limit 1" result set), you can use limit:

select *
from t
order by time desc
limit 3

If you want all records for the three most recent time stamps, you can use a join:

select t.*
from t join
     (select distinct time
      from t
      order by times desc
      limit 3
     ) tt
     on tt.time = t.time

answered Jun 21 '13 at 10:30

Gordon Linoff

1,242,037
58
646
786

Gordon, thanks for your response. Sorry, I might have been slightly unclear in my examples. The second snippet was to show what I wanted if I was to set a limit of 1 (I did that just so that result set could be small). The first snippet shows what the current SQL statement returns. Note that it `unions` the tables 'ticket_change' and 'attachment' etc. That functionality would need to remain. – Ian Clark Jun 21 '13 at 10:44

Limit results to x groups

2 Answers2