Code conversion from Access to T-SQL

I have an Access database that I'm converting to SQL Server 2008. One of the queries in the database uses the LAST function. The table has a AutoNumber ID so true order is simple.

SELECT tbl_unit.unit,
       LAST(tbl_unit.date)     AS Date,
       LAST(tbl_unit.mileage)  AS Mileage
FROM   tbl_unit
GROUP  BY tbl_unit.unit

SQL 2008 doesn't have any function like that so I wrote the following:

SELECT Unit, [Date], Mileage
FROM (
    SELECT a.id, a.Unit, a.[Date], a.Mileage
    FROM tbl_Unit a
        INNER JOIN (SELECT MAX(id) MaxID, Unit FROM tbl_Unit
                    GROUP BY Unit) b ON a.id = b.maxid
) t1

I'm looking for alternates or better code.

This query returns one record for each "Unit" where the Record is the LAST record entered regardless of the date or mileage values entered. The Table has several thousand entries and this query returns over 100 rows.

The ID is both an auto incrementing number and the primary key. The following data is part of the table.

ID      Unit    DATE        Mileage
217316  171     2006-01-27  59761
216668  171     2005-12-01  57875
216194  171     2006-01-21  59346
217591  1127    2006-01-30  406692
217467  1127    2006-01-27  406339
217466  1127    2006-01-27  406127
217598  2310    2006-01-29  68372
217505  2310    2006-01-28  68187
217504  2310    2006-01-28  67987

The correct output with this set of data is:

Unit    Date        Mileage
171     2006-01-27  59761
1127    2006-01-30  406692
2310    2006-01-29  68372

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.

StackExchange Code Review Q#32940, answer score: 3