Getting the latest dated Row for each user in a table

Hi all,
I want to have a query that will return me a single row for each userfrom a table where the table has many rows for each user. The singlereturned row for each user must be the most recently dated entry([I7-Change-Date]) for that user.
An example of the code I have so far is as follows, but it obviously doesn;t work.

select DISTINCT([I1-Customer-Ref]) AS Cust,
([i7-w-fixed-amnt]) as WaterFixedAmt,
([i7-w-rv-amnt]) as WaterRVAmt,
([i7-s-fixed-amnt]) as SewerageFixedAmt,
 ([I7-Change-Date]) AS [Date]
from r07UnMeasuredBills
ORDER BY Cust, [I7-Change-Date] DESC

I am using MS SQL Server 7 for this.
Thanks
Tryst
Have you tried aggregate functions?
i.e.
SELECT I1-Customer-Ref,Max(I7-Change-Date) FROM rO7UnMeasuredBills GROUP BY I1-Customer-Ref

|||

SELECT r.*

FROM r07UnMeasuredBills r

WHERE r.I7-Change-Date IN (SELECT I7-Change-DATE FROM r07UnMeasuredBills r2 WHERE r.I1-Customer-Ref=r2.I1-Customer-Ref)

|||No, I haven't tried that.
Will MAX work on dates (DATETIME)?
(I can't check it now as I am not in work)
Tryst
|||

Yes, infact I forgot to actually do that in my previous post, bad me... It should have been

SELECT r.*

FROM r07UnMeasuredBills r

WHERE r.I7-Change-Date IN (SELECT max(I7-Change-DATE) FROM r07UnMeasuredBills r2 WHERE r.I1-Customer-Ref=r2.I1-Customer-Ref)

|||Hi Motely, thanks for the reply.
Regarding the SQL query you provided, although it works (well, I hope, I haven't tried it yet :)), I am trying to get an understanding of how it works, but the sub-SELECT is confusing me a little. If your using the MAX function, won't that just bring back the row with the most recent date from all the rows and not for each user in the table? Would you be able to just break it down for me, so I can get an understanding of it?
Thanks once again.
Tryst
|||

It woul, however the table in the Sub Query is matched to the customer-ref in the where clause:

r.I1-Customer-Ref=r2.I1-Customer-Ref