Returning a result set with multiple rows based on max date I have a child table that is something like this:

[Cust Date Table]

| Customer ID | Some Date  | Balance |
+-------------+------------+---------+
|           1 | 2012-04-30 |   20.00 |
|           1 | 2012-03-31 |   50.00 |
|           2 | 2012-04-30 |    0.00 |
|           2 | 2012-03-31 |   10.00 | 
|           3 | 2012-03-31 |   60.00 |
|           3 | 2012-02-29 |   10.00 |


I would like to be able to get a result set like this - one record for each client with the latest date:

| Customer ID | Some Date  | Balance |
+-------------+------------+---------+
|           1 | 2012-04-30 |   20.00 | 
|           2 | 2012-04-30 |    0.00 |
|           3 | 2012-03-31 |   60.00 |


I know that I can do this for each individual "Customer ID" with the following SQL (SQL Server syntax):

select top 1  [Some Date], [Customer ID], [Balance]
from [Cust Date Table]
where [Customer ID] = 2
order by [Some Date] desc


| Customer ID | Some Date  | Balance |
+-------------+------------+---------+
|           2 | 2012-04-30 |    0.00 |


But I'm not sure how to get all three of the records I want.  I'm not sure if this is a situation that calls for a sub-query or something else.  

Please note that the max date can be different for any given [Customer ID], (in this example, customer 3's maximum date is 2012-03-31 whereas the other records have a max date of 2012-04-30).
I have tried

select [Customer ID], MAX([Some Date]) AS [Latest Date], Balance 
from [Cust Date Table] 
group by [Customer ID], Balance; 


The problem is this doesn't return just the one row for each customer - it returns multiple rows.
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;