SQL is great, cause there are tons of ways to write something. The one above will work, but I prefer the syntax below.
SELECT (SELECT MAX(Date)
FROM #ListOfTrans T2
WHERE T2.UserName = T1.UserName) Date,
UserName,
Amount,
PID
FROM #ListOfTrans T1
One thing to note here, if this table is going to be large, this query could get slow. You should consider adding an index on the table of UserName and Date column.
Hogan