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.

• Dave: The difference between mine and yours is the sub-query. FYI: I built mine from the inside out which is the reason I left the "Unit" in the query.

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.

• Jeff Vanzella: Can you please explain why you are suggesting it, and what improvements it would make? It makes understanding the answer much easier.
• Malachi: might want to explain the importance of the ORDER BY a.id DESC in this Query as well. I know it is a simple Query but understanding the simple queries is where the advanced stuff becomes easier to understand.
• AgentSQL: Code Project links mention about RANKING functions too. You can just Google them for more information.
• Dave: Although I prefer this solution, it doesn't work. I have been restricted to SQL 2000 for about 10 years and haven't been able to use CTE like this.

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?

• Dave: Yep, looked at those. The data is particularly bad and has many lazy entries in it. First one won't work since ID is an AutoNumber. (Only get one record) 2nd won't work since data is frequently bad.
• Malachi: you should probably work on cleaning up the data first, otherwise you won't really know what is efficient and what is caused by bad data that shouldn't be there in the first place
• Dave: When I rebuild the application I will have appropriate data testing in the input. Cleanup is out of the question. Too much data and too many lazy entries.
• Malachi: could you elaborate on the bad data? you should be able to work around the bad data while using a query similar to the second query. if that is what you want you should either fix or work around the bad data.