SQL Max(date) without group by

Question

I have the following table...

MemberID  ServDate
001       12-12-2015
001       12-13-2015
001       12-15-2015
002       11-30-2015
002       12-04-2015

And I want to make it look like this...

MemberID  ServDate     LastServDate
001       12-12-2015   12-15-2015
001       12-13-2015   12-15-2015
001       12-15-2015   12-15-2015
002       11-30-2015   12-04-2015
002       12-04-2015   12-04-2015

Is there a way I can do this without having to use a GROUP BY or nested query? (I'm dealing with a very large database and the GROUP BY slows things down considerably)

Answer 1

SELECT 
  MemberID, ServDate, 
  MAX(ServDate) OVER (PARTITION BY MemberID) AS LastServDate
FROM Table

Standard SQL, so works in most modern RDBMS (including SQL Server and Oracle).

EDIT by the way, if you want to learn more: MSDN ref. for OVER