Selecting the latest record

[goofaholix]

I'm sure there must a way to do this as one select statement but it escapes me.

What I need to do is select that latest record for each customer based on date.

What I've been doing is creating a View that selects customer, max(date) from table1 grouped by customer, then joining that to table1 on date and customer to pull out the latest record.

Is there a way to do that on the fly as one select?

Thanks.

 

[PHV]

SELECT A.*
FROM table1 A INNER JOIN (
SELECT customer, MAX(date) MaxDate FROM table1 GROUP BY customer
) M ON A.customer = M.customer AND A.date = M.Maxdate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886