sql server - 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 therefore I may have:

  introduced ID Date Status Position Position 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 Pause  

What I want to do is the following: all on one record Alen, where [status] = 'Start'.

When this is done, the part that I do not know is the following: I want to update [status date] and [status] with the last status date. To wit. What do I need:

  ID start date position status status 123 01/01/2009 01/07/2009 Stop  

Any suggestions?

[edit]

I am using SQL Server 2008

By putting it in, I mean create a new table and In it there are only those records which "to get a table with unique ID

I'm not sure if Do you want the latest date and status for any ID?

It will:

  SELECT a.ID, a.StatusDate, a. As an INNER JOIN from the status table (select ID, Max (Statement) ID by AS Statement of Table Group) AID = B.ID and A. Statsat = B. Statsat Date as B.TetSetDet  

Now, what Is the table you want to update with this? You need to put one on all records where [condition] = 'start' "? Do you create a second table and include this date? < / P>

Edit:

OK, so let me Do you think you want to update the data in this newly created table?

Try it (I hope there is no syntax error in it, I do not have a SQL server here, try it right now ):

  Include CD = A in the form of C INNER as UPDATE c SET c.StatusDate = A.StatusDate, c.Status = a.Stateus NewTable in the table. Include .ID in Interval (Selection ID, Max (Position Date) ASD as Table Group According to by ID) KID = BID and a.StatusDate = b.StatusDate