SQL: how to select a single record for multiple identifiers based on max datetime?

Question

I have the following SQL table,

Id WindSpeed DateTime -------------------------------------- 1 1.1 2009-09-14 16:11:38.383 1 1.9 2009-09-15 16:11:38.383 1 2.0 2009-09-16 16:11:38.383 1 1.8 2009-09-17 16:11:38.383 1 1.7 2009-09-19 16:11:38.382 2 1.9 2009-09-19 16:11:38.383 1 1.6 2009-09-19 16:11:38.383 2 1.2 2009-09-20 16:11:38.383 

I want to write a query that will return me the following result from the above table:

 Id WindSpeed DateTime -------------------------------------- 1 1.6 2009-09-19 16:11:38.383 2 1.2 2009-09-20 16:11:38.383 

The above repetition contains the last (based on the last time datetime for this id) single entry. This means that I have several date ids.

I want to get the last single record of all identifiers.

Answer 1

 SELECT a.Id, a.WindSpeed, a.DateTime FROM YourTable AS a INNER JOIN ( SELECT ID, Max(DateTime) AS DateTime FROM YourTable GROUP BY ID ) AS b ON a.ID = b.ID AND a.DateTime = b.DateTime 

Answer 2

 SELECT t1.Id, t1.WindSpeed, t1.DateTime FROM table1 As t1 WHERE t1.DateTime = (SELECT Max(DateTime) FROM table1 As t2 WHERE t2.ID = t1.ID) 

Answer 3

This should also do what you want:

 SELECT ID, WindSpeed, [DateTime] FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [DateTime] DESC) AS RowNumber, Id, WindSpeed, [DateTime] FROM MyTable ) T WHERE RowNumber = 1