Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 6 down vote accepted

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;
share|improve this answer
    
Oh - you've changed the question? –  Rob Farley May 1 '12 at 2:59
    
Changed my answer now. –  Rob Farley May 1 '12 at 3:03
    
Ok, that works. Thanks. –  Joe DBA May 1 '12 at 15:01
add comment

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.

share|improve this answer
    
This answer is also correct. Unfortunately I don't have enough rep to upvote it and I have to choose one answer. –  Joe DBA May 1 '12 at 15:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.