How do I keep a fixed list of rows in a table?

[Alan Williamson]

Probably not making myself clear with that subject. Here is what I
want to do.

I want to basically implement a FIFO (FirstInFirstOut) queue in SQL.
So I want to keep adding to a given table, but only have it keep the
last say 25 items. Anything older is removed. I have been trying to
do some stuff with the DELETE statement, but I am going no where.

So say a table is setup for multiple users:

Column_User
Column_Item
Column_Date (? probably need this column)

I want there ever to be only 25 rows stored for a given user; so when I
INSERT a new item, I would need to remove/trim the items. Its this
removing of the other items that is causing me a bit grief, as I can't
seem to do it without doing another SELECT.

Thoughts? Suggestions?

Thanks

[Bill Karwin]

Alan Williamson wrote:

> I want there ever to be only 25 rows stored for a given user; so when I
> INSERT a new item, I would need to remove/trim the items.

MySQL implements as an extension to the SQL standard ORDER BY and LIMIT
clauses for the DELETE statement:

1. Start transaction
2. SELECT COUNT(*) FROM tablename WHERE column_user = 'Alan';
3. If count >= 25, DELETE FROM tablename
WHERE column_user = 'Alan'
ORDER BY column_date ASC
LIMIT ($count-24);
You must do the $count-24 calculation before you create the SQL
statement, because LIMIT accepts only an integer, not an expression.
4. INSERT INTO tablename <one new row for user 'Alan'>;
5. COMMIT;

Regards,
Bill K.

[Philipp Tölke]

Alan Williamson <alan@blog-city.com> schrieb:

> I want there ever to be only 25 rows stored for a given user; so when I
> INSERT a new item, I would need to remove/trim the items. Its this
> removing of the other items that is causing me a bit grief, as I can't
> seem to do it without doing another SELECT.

In mySQL 5 it might be possible to create an update trigger, which
deletes old rows.

Cheers,
--
Philipp Tölke
PGP: 0x96A1FE7A