0

Using SQL Server 2000

Table

ID Date Value

001 23-02-2009 300
001 24-02-2009 400
001 25-02-2009 150
002 23-02-2009 300
002 24-02-2009 400
003 23-02-2009 150
.....
.....

From the above table i want to delete the last date for each id.

How to make a query

Expected output

 ID Date Value

    001 23-02-2009 300
    001 24-02-2009 400
    002 23-02-2009 300
    .....
    .....
    .....

Need Query Help

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Gopal
  • 11,712
  • 52
  • 154
  • 229
  • 1
    possible duplicate of [SQL Server ROW_NUMBER() on SQL Server 2000?](http://stackoverflow.com/questions/4081753/sql-server-row-number-on-sql-server-2000) – OMG Ponies Nov 03 '11 at 04:31

1 Answers1

3

You can achieve this by doing a subselect and a join in the DELETE statement. For example:

DECLARE @myTable TABLE(ID VARCHAR(3), [Date] DATETIME, Value INT)
INSERT INTO @myTable VALUES('001', CONVERT(DATETIME, '23-02-2009', 103), 300)
INSERT INTO @myTable VALUES('001', CONVERT(DATETIME, '24-02-2009', 103), 400)
INSERT INTO @myTable VALUES('001', CONVERT(DATETIME, '25-02-2009', 103), 150)
INSERT INTO @myTable VALUES('002', CONVERT(DATETIME, '23-02-2009', 103), 300)
INSERT INTO @myTable VALUES('002', CONVERT(DATETIME, '24-02-2009', 103), 400)
INSERT INTO @myTable VALUES('003', CONVERT(DATETIME, '23-02-2009', 103), 150)

DELETE @myTable
FROM @myTable M
JOIN (SELECT ID, MAX([Date]) as [Date] FROM @myTable GROUP BY [ID]) G
    ON G.ID = M.ID AND G.[Date] = M.[Date]

SELECT * FROM @myTable

Please note that I tested this SQL against SQL Server 2005, but I believe it should work in SQL Server 2000 as well.

rsbarro
  • 27,021
  • 9
  • 71
  • 75
  • 2
    +1 Probably the way to go but watch out if there's no unique constraint on {ID, Date} – Conrad Frix Nov 03 '11 at 04:45
  • @Conrad Good point. If {ID, Date} are not unique then this problem becomes much more difficult in SQL Server 2000. The link that OMG Ponies posted in the comment to the question explains why. In that case, I suppose a ROW_NUMBER solution could be used in SQL Server 2005 onward. – rsbarro Nov 03 '11 at 04:57