SQL Max(date) without group by

10,137
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

Share:
10,137
J.Doe
Author by

J.Doe

Updated on June 16, 2022

Comments

  • J.Doe
    J.Doe almost 2 years

    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)