MSSQL Select Syntax Help

I have a table that has unit id, date, time, etc. I would like to select each unit id with the last date it has in the table. The result should have each unit listed once with the latest date in the table.

For Example:

unit id Date Time
00100 01/12/2007 8:00
00100 01/12/2007 8:45
00200 01/12/2007 8:50
00100 01/13/2007 13:30
00300 01/13/2007 13:45
00100 01/14/2007 11:00
00200 01/14/2007 11:30

the result should be:

00100 01/14/2007 11:00
00200 01/14/2007 11:30
00300 01/13/2007 13:45

Try this (Let's call your table "T")

SELECT [unit id],Max([Date Time])

FROM T

GROUP BY [unit id]

|||

Thanks for the response.

That is very close to what I need. But, if I had additional fields that I needed such as address and speed how would I include those in the select statement. I really couldn't use max because that would give me the highest speed. I need the address and speed that goes with the MAX(DATE time)

Sorry I didn't include that bit of information first.

|||

In that case, one option would be to take advantage of derived table concept as follows (Note:[Date Time] column is assumed to have unique values)...

SELECT T.* FROM T INNER JOIN (SELECT [unit id],[Date Time]=Max([Date Time]) FROM T GROUP BY [unit id]) as A ON T.[Date Time]=A.[Date Time]

|||

Thanks giddyup.

Your suggestion helped out a lot.

This is what I got to work.

SELECT * FROM log as t1
JOIN (SELECT [vehicle number],Max(Date+' '+time) as maxdate FROM log GROUP BY [vehicle number]) AS t2
ON t1.[vehicle number] = t2.[vehicle number] and t2.maxdate = t1.date+' '+t1.time
order by t1.[vehicle number]