Re: [SQL] keeping last 30 entries of a log table

[Jeff Frost]

On Wed, 21 Jun 2006, Ben K. wrote:

Just for the sake of alternatives -
create sequence cy30 maxvalue 30 cycle;

insert into log values(select generate_series(1,30), 'dummy');
INSERT 0 30

update log set des='....' where account_id=(select nextval('cy30'));
UPDATE 1


There are details to consider I guess. For example what if an update fails 
and the sequence already advanced... Also, since we cycle the id, for 
sorting, we'll need to add timestamp or something similar.


Only problem with this approach is that I need to be able to keep track of the 
last 30 items per each account_id...so each account_id will have his last 30 
messages in the table.


--
Jeff Frost, Owner       <[EMAIL PROTECTED]>
Frost Consulting, LLC   http://www.frostconsultingllc.com/
Phone: 650-780-7908     FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match