Code conversion from Access to T-SQL

Question

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

Answer 1

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.

Answer 2

Try this -

;WITH cte AS (
    SELECT a.id, a.Unit, a.[Date], a.Mileage, ROW_NUMBER() OVER (ORDER BY a.id DESC) row_num FROM tbl_Unit a
)
SELECT * FROM cte WHERE row_num = 1

Check Tech net Document - CTE

Beside these you can also check some code project links, these are helpful.

CTE in SQL Server

FOR ROW_NUMBER check documentation you can find examples there which I think you can understand easily.

CTE in SQL Server 2008

You can find details on CTE in these links. It also provides you information on when to use them. When you get familiar with the purpose and the syntax of CTE, you can figure out different use of CTE yourself.

Answer 3

Have you Tried this?

SELECT Unit, [Date], Mileage
FROM tbl_unit
WHERE tbl_unit.id = (SELECT MAX(id) FROM tbl_unit)

OR

SELECT Unit, [Date], Mileage
FROM tbl_unit
WHERE tbl_unit.Date = (SELECT MAX(Date) FROM tbl_unit)

I am not sure exactly what kind of Record set you are looking for, could you give a little more information if this is not useful to you?