Very Computer

Field1 varchar(10)   NOT NULL
Field2 varchar(10)   NOT NULL
Field3 smalldatetime  NOT NULL

how can i delete the record with the oldest date ?

CREATE TAble #tblDemo
(Field1 varchar(10)   NOT NULL,
Field2 varchar(10)   NOT NULL,
Field3 smalldatetime  NOT NULL)

INSERT INTO #tblDemo
VALUES('ABC','XYZ','02/01/2004')

INSERT INTO #tblDemo
VALUES('ABC','XYZ','02/02/2004')

INSERT INTO #tblDemo
VALUES('ABC','XYZ','02/03/2004')

INSERT INTO #tblDemo
VALUES('ABC','XYZ','02/04/2004')

SELECT * FROM #tblDemo

DELETE FROM #tblDemo
WHERE Field3 = (SELECT Min(Field3)
FROM #tblDemo)

SELECT * FROM #tblDemo

DROP TABLE #tblDemo

--
Roji. P. Thomas
SQL Server Programmer

Quote:> i have 10 records in a table

> Field1 varchar(10)   NOT NULL
> Field2 varchar(10)   NOT NULL
> Field3 smalldatetime  NOT NULL

> how can i delete the record with the oldest date ?

delete tblname
where dt_colname in (select min(dt_colname)
from tblname )

thx
rahul nagarmat
sql server mvp
intel corp.