MAX and GROUP BY, but get MAX record?

Question

I have table [Customers] that looks like this:

clientID |    CreateDate    |  CardType   |     Paymethod
59       |    2012-04-24    |      NULL   |       CHECK
59       |    2011-03-24    |      1      |       VISA
59       |    2010-02-24    |      2      |       MC
59       |    2009-01-24    |      1      |       VISA
50       |    2012-05-30    |      1      |       AMEX
50       |    2009-01-01    |      1      |       VISA
50       |    2008-01-01    |      NULL   |       CHECK

I need to group this data so that I get the MAX(createDate) for each clientID, but return the data associated to the record that had the MAX CreateDate.

So for example, I would like to group by clientId so that the final recordset looks like this:

clientID |   CreateDate    |  CardType   |   Paymethod
59         |  2012-04-24     |      NULL       |       CHECK
50         |  2012-05-30     |      1             |       AMEX

Any help would be greatly appreciated.

Thanks.

Answer 1

If you are using SS 2005 or greater, then use a ranking function.

with R as (
select *, row_number() over(partition by clientID order by CreateDate DESC) as rn
from T
)
select *
from R
where rn = 1;

If you can have ties (multiple rows with same [CreateDate] for the same [clientID]), then you will need to find a tie breaker.

---

AMB

Some guidelines for posting questions...

Answer 2

Another way of doing it would be:

SELECT C.clientID,C.CreateDate ,C.CardType,C.Paymethod

FROM Customers C

CROSS APPLY 

(SELECT MAX(CreateDate)CreateDate, ClientId FROM Customers WHERE CLientId = C.ClientId

GROUP BY ClientId) T

WHERE T.ClientId = C.CLientId AND T.CreateDate = C.CreateDate

---

Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

Answer 3

CREATE TABLE TMPTABLE1
(clientID NUMERIC, CreateDate DATETIME, CardType NUMERIC, Paymethod VARCHAR(10))

--INSERT INTO TMPTABLE1 VALUES
--(59,'2012-04-24',NULL,'CHECK')

--INSERT INTO TMPTABLE1 VALUES
--(59,'2011-03-24',1,'VISA')
--INSERT INTO TMPTABLE1 VALUES
--(59,'2010-02-24',2,'MC')
--INSERT INTO TMPTABLE1 VALUES
--(59,'2009-01-24',1,'VISA')

 

--INSERT INTO TMPTABLE1 VALUES
--(50,'2012-05-30',1,'AMEX')
--INSERT INTO TMPTABLE1 VALUES
--(50,'2009-01-01',1,'VISA')
--INSERT INTO TMPTABLE1 VALUES
--(50,'2008-01-01',NULL,'CHECK')

select
clientID,CreateDate,CardType,Paymethod
from (select clientID, CreateDate, CardType, Paymethod, max(CreateDate) over (partition by clientID) max_CreateDate
from   TMPTABLE1)  tt
where tt.CreateDate = tt.max_CreateDate

---

Trilok Negi