How to mantain only the last 100 sales in a historical table?

[Former Member]

Hi, I would llike to mantain a table that contains only the last 50 sales per warehouse and sku. The table would be something like:

WarehouseId (the warehouse where the sale ocurred)
SkuId (the article being sold)
SalesDateTime ClientId
SalesQty SalesCost
SalesPrice
TransactionId (the transaction number)
TransactionRowNumber (the transaction row number)

I would like to only keep the last 50 sales for each WarehouseId/SkuId.

Reading the sales directly from the transactions would be costly because the transactions contain other kinds of transactions (purchases, transfers, adjustments), and at least 3 joins are necessary to determine which rows correspond to sales. Because of this, I was thinking that if I could mantain the last 50 sales in a separate table, I could more efficiently work with them.

The only idea I have is to have a row counter column, and when it reaches 50, delete the first one, renumber the records and insert the new #50. But this idea seems like a mess....

Any ideas on how to accomplish this task of mantaining only an "X" number of records for a given set: In this case 50 records per WarehouseId/SkuId.

Thanks, Edgard

[justin_willey]

You could do it with an AFTER trigger on an appropriate table that would insert the new sale with all relevant details into your "last 50 Sales" table and delete the oldest. You can use the SELECT TOP X syntax to establish which are your 50 newest sales.

However - do you really need to do this? 3 joins is not many tables for SQL Anywhere to deal with. We are often running queries with between ten and twenty tables involved and expect to do it in real time (with hundreds of users). You just need to be careful in your design of tables and indexes and also in your approach to locking (ie use isolation level 0).

[VolkerBarth]

I second Justin's hint.

In case you really really need to store the last n rows in a separate table, you might also think of a materialized view. That would "update" the according data automatically when specified accordingly. Note, locking issues still need to be considered carefully.