Selecting the latest record based on DATETIME

Question

I need to select the latest record per memberid by DATETIME.

SAY my table is like this:

MemberID    CreatedDate
188         2013-01-31 08:10:15.990
188         2013-01-31 08:10:15.970
188         2013-01-24 19:36:01.840
244         2013-01-24 19:36:01.840
244         2013-01-31 08:10:15.970
244         2013-01-31 08:10:15.980

OUT:

188 2013-01-31 08:10:15.990
244 2013-01-31 08:10:15.980

SQL QUERY to select the latest based on date-Time-milliseconds.

Thank in advance for the help.

Have you tried writing anything to get your desired output? – Ellesedil Jan 13 '14 at 14:13 — Ellesedil, Jan 13 '14 at 14:13

score 3 · Answer 1 · answered Jan 13 '14 at 14:16

3

I think this is what you are looking for, you need to group the memberID and Find the max CreatedDate

Select memberId, max(CreatedDate) from members
Group By memberId

answered Jan 13 '14 at 14:16

Lawrence Thurman

657
8
15

score 2 · Accepted Answer · answered Jan 13 '14 at 14:17

The simplest is using a ranking function like ROW_NUMBER:

WITH CTE AS
(
   SELECT MemberID, CreatedDate, 
          RN = ROW_NUMBER() OVER (PARTITION BY MemberID 
                                  ORDER BY CreatedDate DESC)
   FROM dbo.TableName  
)
SELECT MemberID, CreatedDate FROM CTE WHERE RN = 1

This just returns one record per MemberID (the one with the latest CreatedDate). If you have multiple latest records an arbitrary is taken. If you want to take all in this case you have to replace ROW_NUMBER with DENSE_RANK.

score 1 · Answer 3 · answered Jan 13 '14 at 14:16

1

Is this you are looking for.

 select memberid,max(createddate)  createddate from Table
    group by memberid

answered Jan 13 '14 at 14:16

Kishore

846
6
9

Selecting the latest record based on DATETIME

3 Answers3