Last value entered based on last modified date

  • I need to get the last modified value for each project and each owner

    -- SAMPLE DATA

    DECLARE @Audit TABLE (

    [ID] varchar(50),

    [Owner] varchar(50),

    [Modified on] datetime,

    [Value] money

    )

    SET DATEFORMAT dmy

    INSERT INTO @Audit

    SELECT 'ProjA','OwnerA','20/05/2011 17:45','1256'

    UNION ALL SELECT 'ProjA','OwnerA','11/07/2011 16:22','2540'

    UNION ALL SELECT 'ProjB','OwnerB','21/06/2011 09:32','25610'

    UNION ALL SELECT 'ProjB','OwnerB','21/06/2011 09:37','30000'

    UNION ALL SELECT 'ProjB','OwnerB','21/06/2011 09:40','45000';

    -- SOLUTION

    WITH AuditData AS (

    SELECT *,

    RN = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [Modified On] DESC),

    max([Modified on]) OVER(PARTITION BY ID) AS [Max Modified On]

    FROM @Audit

    )

    SELECT [ID], [Owner], [Modified on], [Value], [Max Modified On]

    FROM AuditData AS AD

    I need the following result:

    [ID], [Owner], [Modified on], [Value], [Max Modified On]

    ProjAOwnerA2011-07-11 16:22:00.0002540.002011-07-11 16:22:00.000

    ProjBOwnerB2011-06-21 09:40:00.00045000.002011-06-21 09:40:00.000

    Instead I get the following:

    ProjAOwnerA2011-05-20 17:45:00.0001256.002011-07-11 16:22:00.000

    ProjAOwnerA2011-07-11 16:22:00.0002540.002011-07-11 16:22:00.000

    ProjBOwnerB2011-06-21 09:32:00.00025610.002011-06-21 09:40:00.000

    ProjBOwnerB2011-06-21 09:37:00.00030000.002011-06-21 09:40:00.000

    ProjBOwnerB2011-06-21 09:40:00.00045000.002011-06-21 09:40:00.000

    What am I doing wrong?

    Your help is appreciated.

    Many Thanks

    A

  • WITH AuditData AS (

    SELECT *,

    RN = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [Modified On] DESC),

    max([Modified on]) OVER(PARTITION BY ID) AS [Max Modified On]

    FROM @Audit

    )

    SELECT [ID], [Owner], [Modified on], [Value], [Max Modified On]

    FROM AuditData AS AD

    WHERE RN = 1;

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Or this maybe:

    with MaxDates as (select id, MaxMod = max([Modified on]) from @Audit a group by id)

    select a.id,a.Owner, a.[Modified on],a.Value

    from @Audit a

    join MaxDates m on a.id = m.id and a.[Modified on] = m.MaxMod

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply