Post reply

Updating all rows for a group based on value of maximum

  • October 20, 2005 at 2:17 pm

    #166477

    I have a table:

    File     Extension       Version    Type
    AA       DOC             3          WORD
    AA       NULL            2          NULL
    AA       NULL            1          NULL
    BB       DOC             1          WORD
    CC       XLS             2          EXCEL
    CC       NULL            1          NULL
     

    As you can see, the highest version of each file has the Type for all versions of that file.  I need to update the Type field for all versions of the document with the Type from the highest version:

    File     Extension       Version    Type
    AA       DOC             3          WORD
    AA       NULL            2          WORD
    AA       NULL            1          WORD
    BB       DOC             1          WORD
    CC       XLS             2          EXCEL
    CC       NULL            1          EXCEL

    I've always found this type of query very difficult -- dealing with WHERE vs. HAVING and GROUP BY along with an UPDATE.  Can someone please help?

    Thanks,

    Mark

  • October 20, 2005 at 3:07 pm

    #598949

    See if this helps....

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=228725#bm229100

     

    I wasn't born stupid - I had to study.

  • October 20, 2005 at 3:12 pm

    #598952

    Update a Set Type = b.Type

    FROM

         TABLESOURCE a

         join

         ( Select File, Type, Max(Version)

            FROM TABLESOURCE

            WHERE Type is not null -- With this you may not need max()

            GROUP BY File, Type

         ) on a.File = b.File

     


    * Noel

  • October 20, 2005 at 3:35 pm

    #598959

    Tested it and it needed a couple of 'slight' tweaks:

    DECLARE @TableSource TABLE( [File] varchar(5),

                                               Extension varchar(5),

                                               Version integer, 

                                              Type varchar(10))

    INSERT INTO @TableSource

    SELECT 'AA', 'DOC', 3, 'WORD'   UNION ALL

    SELECT 'AA', NULL, 2, NULL      UNION ALL

    SELECT 'AA', NULL, 1, NULL      UNION ALL

    SELECT 'BB', 'DOC', 1, 'WORD'   UNION ALL

    SELECT 'CC', 'XLS', 2, 'EXCEL'  UNION ALL

    SELECT 'CC', NULL, 1, NULL

    UPDATE a SET

        Type = b.Type

    FROM @TableSource a

       JOIN( SELECT [File], Type, MAX( Version) AS MaxVersion

             FROM @TableSource 

             WHERE Type IS NOT NULL -- With this you may not need max()

             GROUP BY [File], Type) b ON a.[File] = b.[File]

    SELECT * FROM @TableSource

     

    (And you are correct - you do not need MAX..., but if you use it, that column must be named...) 

    I wasn't born stupid - I had to study.

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

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