Code conversion from Access to T-SQL
This should do it:

SELECT U1.Unit, U1.[Date], U1.Mileage
FROM tbl_unit U1
WHERE U1.id IN (
    SELECT MAX(U2.id) 
    FROM tbl_unit U2
    GROUP BY U2.Unit
);


This uses a simple subquery correlated on the tbl_Unit.Unit.  This approximates your GROUP BY and LAST() functions in Access by SELECTing the most recent row for each Unit.  LAST() in Access returns the most recently added record, and since the id field is an Autonumber (or IDENTITY() in SQL Server) field, MAX(id) will automatically return the most recent row for each unit.