Returning a result set with multiple rows based on max date
You simply want:
SELECT
[Customer ID],
MAX([Some Date]) AS[Latest Date]
FROM[Cust Date TABLE]
GROUP BY
[Customer ID];
Ok - you've revised it. You now want to order the rows and pick the top one:
WITH numbered AS (
SELECT
[Customer ID],
[Some Date],
[Balance],
ROW_NUMBER() OVER (
PARTITION BY
[Customer ID]
ORDER BY
[Some Date] DESC
) AS rownum
FROM[Cust Date TABLE]
)
SELECT
[Customer ID],
[Some Date],
[Balance]
FROM numbered
WHERE
rownum = 1;
I think you're after something like this
select c.[customer ID], [some date], balance
from [cust date table] c
inner join
( select [customer ID], MAX([some date]) as maxdate
from [cust date table]
group by [customer ID]) c2
on c2.[customer ID] = c.[customer ID]
and c2.maxdate = c.[some date]
There are a number of variations on this, i.e. CTE, table variable, #table, that you can play around with to see what gives you the best performance in your situation.