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