Returning a result set with multiple rows based on max date

Question

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.

Answer 1

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;

• Tim: One advantage (or disadvantage, depending on your requirements) of this solution is that if the latest date occurs in more than one row for the same customer, it won't produce duplicate results.

Answer 2

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.