current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 3 down vote favorite

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.
Edit: 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.
Edit: The ID is both an auto incramenting 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
share|improve this question
    
do you have the schema of the table? is id an auto incremented number or a primary key? –  Lyle's Mug Oct 23 '13 at 13:24

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
    
This will return only the last record, not the 3 records requested. –  Dave Oct 26 '13 at 3:12
    
You're correct. I've edited my answer to correct the issue. –  Max Vernon Oct 26 '13 at 4:26
    
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. –  Dave Oct 29 '13 at 18:40
up vote 2 down vote

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.

share|improve this answer
2  
Can you please explain why you are suggesting it, and what improvements it would make? It makes understanding the answer much easier. –  Jeff Vanzella Oct 21 '13 at 22:50
    
First thing that came to my mind when suggesting CTE is code readability. Second, @Dave seems new to SQL Server, so it might just interest him and he could know about recursive query. –  AgentSQL Oct 21 '13 at 23:18
    
please add that into the answer, if you have a link that would be awesome too. I know enough SQL to get myself into Trouble so reading more SQL Material would be very helpful –  Lyle's Mug Oct 22 '13 at 13:43
    
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. –  Lyle's Mug Oct 22 '13 at 13:47
1  
Code Project links mention about RANKING functions too. You can just Google them for more information. –  AgentSQL Oct 22 '13 at 13:58
up vote 1 down vote

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)

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?

share|improve this answer
    
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. –  Dave Oct 23 '13 at 0:16
    
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 –  Lyle's Mug Oct 23 '13 at 13:25
    
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. –  Dave Oct 23 '13 at 15:41
    
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. –  Lyle's Mug Oct 23 '13 at 15:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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