Delete Row from Table 1

[CrystalVis]

I've a table that will always stored ten rows of data. I've a DTS package that run weekly to populate this table. What I'm trying to accomplish is remove the oldest row from the table in order to keep the number of row = 10 at all time.

For example:

As_of_Date Category
9/11/2003 Hungry Howie
9/18/2003 Papa John
9/25/2003 Domino Pizza
10/2/2003 Pizza Hut
10/9/2003 Littel Ceasar
.... .............
11/13/2003 ABC Pizza

I want to remove the 9/11/2003 from the table. As_of_Date is a true datetime field. There is no key field in this table. I have tried to use the MIN function on the As_of_Date, but it doesn't work. Can someone please help. Thanks

 

[ClaireHCM]

Try this

delete tb1 where As_of_Date = (select top 1 As_of_date from tb1 order by as_of_date)

 

[LogicalmanUS]

CrystalVis,

You don't state whether the DTS package returns 11 rows only. If this is so, then the previous answer is perfect, if there could be more than 11 rows returned, then you may need something like:

DELETE tb1 WHERE As_of_Date NOT IN (SELECT TOP 10 As_of_date FROM tb1 ORDER BY as_of_date DESC)

Logicalman

 

[CrystalVis]

Thank you all for your helps.

ClaireHsu,
Your reply solve my problem. Thank you very much as always.

LogicalmanUS,
Thanks for your input as well. When the DTS package run on a weekly basis, it will append one additional row to the result table. I want to remove the oldest row so that this table will always contain 10 rows for reporting purpose.