Need a fixed-length table

[SimonPure]

Hi,

Question from a rank beginner:

I need to create tables with a fixed number of rows, lets say 50. When a new row of data is added, the oldest entry disappears. I don't care what happens to the old data, I just need the latest 50.

It would be nice if this could be done without too much overhead. If you can think of more than one way of doing this it would be helpful.

The tables will be used by a web server that will display operational "dashboard" data from various systems.

Thank you for your help,
SimonPure

[almaz]

It's not a recommended pattern for SQL programming. If you want to get latest 50 records, just create a view that selects 50 latest records, and use simple insert statements.

create view dbo.vGetLast50
as
    select top 50 ID, inserted_date, description
    from table_name
    order by inserted_date desc

[iburyak]

If you still insist on your original request do following:


[PHP]Delete from table_name where ID not in
(select top 50 ID
from table_name
order by inserted_date desc)[/PHP]

[SimonPure]

Gentlemen,

Thank you for your advice. We're going to try with SQL, but I'll tell my people that it may not be the best approach.

--SimonPure

[iburyak]

Good Luck.