Last() Function?

Greetings,
I'm having some trouble to create a report. In this report I'm supposed to get the last row for each group of customers, ordered by date. Let me explain you a bit further using an example:
The table contains these fields, amongst others: CustomerID, Car, LastVisit.
So, for each customer there are many rows, each one with a different value in LastVisit. What I need to get, and before I started it looked quite easy, is a recordset with the last visit of each customer, given a specific car. For example, the last visit of each customer that has a Ford.
Now, I think there's a function LAST() in Access that actually gets the last row in a group by. That would be great, because the only thing I would have to do is grouping by CustomerID and getting the said last row, but unfortunatelly looks like SQL Server 2000 doesn't support that function.
The question is, how can I get that last row in each group? I've already tried using cursors in a stored procedure and temporary tables but I'm afraid I need a less time consuming solution as I'm dealing with more than 1.5 million rows.
If that's not possible, I guess I could "mark" the rows that I need and filter it later, but I'm not sure how to do it either...
If anyone around can give me some advice I would really appreciate it. I apologize if I was not very clear. I'll try to clarify it if you have doubts.
Thanks in advance,
Ricardo Lopez.

Two thoughts:

1. Try using MIN and MAX (whichever gives the correct value in this case) it will work on values which are non-numeric aswell.

2. Otherwise, use top with an order by, and use desc or asc as appropriate, and write a series of sql statments to get each group, and then combine using the UNION ALL operator.

|||

This will get you the results. It is using a derived table to get the last visit date for a customer id and then joining it with the same table to get the car of that visit.

select v.CustomerID, v.Car
from Visits v
inner join (select CustomerID as CustomerID, max(LastVisit) as LastVisit
from Visits
group by CustomerID) vlast
on (vlast.CustomerID = v.CustomerID and vlast.LastVisit = v.LastVisit)


I have assumed that LastVisit is a datetime column.