SQL update value to highest date in table

I have the following problem:

Suppose I have a table with the following fields: [ID] [Start Date] [Status Date] [Status Description]

[ID] is not unique, and so I may have:

ID  Start Date   Status Date      Status
123 01/01/2009  01/01/2009       Start
123 01/01/2009  01/02/2009       Change
123 01/01/2009  01/03/2009       Change
123 01/01/2009  01/07/2009       Stop

What I want to do is the foll开发者_开发问答owing: run an insert into on all the records where [Status] = 'Start'.

When that is done, the part that I don't know how to do is then the following: I want to update [Status Date] and [Status] to that of the final status date. I.e. what I want is:

ID  Start Date   Status Date      Status
123 01/01/2009  01/07/2009       Stop

Any suggestions?

[EDIT]

I'm using SQL Server 2008

With insert into I mean create a new table and insert into it only those records that have status "Start", thereby achieving a table with unique IDs

I'm not sure if I get your right. You want the latest date and status for any ID?

That would be:

SELECT    a.ID, a.StatusDate, a.Status
FROM      Table AS a
INNER JOIN 
(
    SELECT   ID, Max(StatusDate) AS StatusDate
    FROM     Table
    GROUP BY ID
) AS b
ON        a.ID = b.ID
AND       a.StatusDate = b.StatusDate

Now, what table do you want to update with this? What do you mean by "run an insert into on all the records where [Status] = 'Start'"? Did you create a second table and insert this date into it?

EDIT:

Okay, so I guess you want to update the data in this newly created table then?

Try this (I hope I have no syntax error in this, I don't have an SQL server here right now to try it):

UPDATE    c
SET       c.StatusDate = a.StatusDate,
          c.Status = a.Status
FROM      NewTable AS c
INNER JOIN Table AS a
ON        c.ID = a.ID
INNER JOIN 
(
    SELECT   ID, Max(StatusDate) AS StatusDate
    FROM     Table
    GROUP BY ID
) AS b
ON        a.ID = b.ID
AND       a.StatusDate = b.StatusDate

I presume you just want to UPDATE all results with Status="Start" to Status="Stop" AND [Status Date] = now() (now() or something similar depending on the database)?

Sorry if that sounds unclear, but I'm not really sure what you are asking.

Why do you need to UPDATE anything? If you can determine from the data which value has the highest date, there is no reason to store it. All that does is introduce the possibility for inconsistency ... now you need to update everything every time a single row is added, changed or deleted. Yuck.

Does this do what you want? It creates a new record for every record that has a status of start and adds a status of stop but only if a stop record does not currently exist.

Insert into my table (ID  Start Date   Status Date      Status)
select ID  Start Date   Status Date      'Stop'
From mytable  mt 
left join mytable mt2 on mt.id = mt2.id and mt2.status = 'stop'
where mt.status = 'Start'
and mt2.id is null