3
\$\begingroup\$

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
\$\endgroup\$
3
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – Dave Oct 29 '13 at 18:40
2
\$\begingroup\$

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.

\$\endgroup\$
  • 2
    \$\begingroup\$ Can you please explain why you are suggesting it, and what improvements it would make? It makes understanding the answer much easier. \$\endgroup\$ – Jeff Vanzella Oct 21 '13 at 22:50
  • \$\begingroup\$ 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. \$\endgroup\$ – Malachi Oct 22 '13 at 13:47
  • 1
    \$\begingroup\$ Code Project links mention about RANKING functions too. You can just Google them for more information. \$\endgroup\$ – AgentSQL Oct 22 '13 at 13:58
  • \$\begingroup\$ 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. \$\endgroup\$ – Dave Oct 29 '13 at 18:38
1
\$\begingroup\$

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?

\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – Dave Oct 23 '13 at 0:16
  • \$\begingroup\$ 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 \$\endgroup\$ – Malachi Oct 23 '13 at 13:25
  • \$\begingroup\$ 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. \$\endgroup\$ – Dave Oct 23 '13 at 15:41
  • \$\begingroup\$ 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. \$\endgroup\$ – Malachi Oct 23 '13 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.