I think what you want is related to this post on how to create a FIFO queue in Postgres:<br /><br /><a
eudora="autourl"
href="http://people.planetpostgresql.org/greg/index.php?/archives/89-Implementing-a-queue-in-SQL-Postgres-version.html">
http://people.planetpostgresql.org/greg/index.php?/archives/89-Implementing-a-queue-in-SQL-Postgres-version.html</a><br
/><br/> The major difference is that you want a FIFO queue per user_id, so the triggering code would want to bump old
recordsaggregating on user_id to calculate the "limit" subquery. His original code is this:<br /><br /><br
/><pre> DELETEFROM q WHERE id NOT IN
(SELECT id FROM q ORDER BY id DESC LIMIT maxrows);
</pre>And that subquery is where (I think!) you'd want to add "where user_id = [user_id]" - I'm not sure how you'll
passuser_id into this function, maybe someone else can help with that?<br /><br /> Hopefully this is useful?<br /><br
/>Steve<br /><br /> At 06:24 AM 1/8/2008, pgsql-sql-owner@postgresql.org wrote:<br /><blockquote cite="" class="cite"
type="cite">Date:Tue, 8 Jan 2008 14:24:22 +0000<br /> From: "Jamie Tufnell" <diesql@googlemail.com><br /> To:
pgsql-sql@postgresql.org<br/> Subject: How to keep at-most N rows per group? periodic DELETEs or constraints or..?<br
/>Message-ID: <b0a4f3350801080624k2088c96aq21c511873959aa87@mail.gmail.com><br /><br /> Hi,<br /><br /> I have a
tablethat stores per-user histories of recently viewed items<br /> and I'd like to limit the amount of history items to
<=50 per user.<br /> I'm considering doing this with a query run from cron every so often<br /> but I'm not happy
withwhat I've come up with so far, and since it's a<br /> quite active table I thought I'd ask here to see if there's a
more<br/> efficient way.<br /><br /> Right now the table structure is as follows...<br /><br /> user_item_history: id
(PK),user_id (FK), item_id (FK), timestamp<br /><br /> For user_ids that have more than 50 rows, I want to keep the
most<br/> recent 50 and delete the rest.<br /><br /> The most obvious way of doing this for me is:<br /><br /> --<br />
--Get the user_ids with 50 or more history entries like this<br /> --<br /> SELECT user_id, count(*)<br /> FROM
user_scene_history<br/> GROUP BY user_id<br /> HAVING count(*) > 50;<br /><br /> --<br /> -- Then iterate the ids
above(_user_id)<br /> --<br /> DELETE FROM user_scene_history<br /> WHERE user_id = _user_id AND id NOT IN (<br />
SELECTid FROM user_scene_history<br /> WHERE user_id = _user_id<br /> ORDER BY timestamp DESC<br /> LIMIT
50);<br/><br /> I've left out the simple logic tying the above two queries together<br /> for clarity..<br /><br /> I
haven'tactually tested this but while I assume it would work I<br /> imagine there is a neater and possibly more
efficientway of attacking<br /> this. I'm also open to different approaches of limiting the user's<br /> history too
...perhaps with table constraints so they can simply<br /> never exceed 50 entries? But I'm not sure how to do
this..<br/><br /> Any help would be greatly appreciated..<br /><br /> Thanks,<br /> Jamie</blockquote>
