Find Current Version

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

/* This table gives us some sample records
     * to experiment with.
     */
    DECLARE @Example TABLE
        (
            ID            INT                PRIMARY KEY,
            [UID]        INT                NOT NULL,
            [Version]    INT                NOT NULL,
            Content        VARCHAR(255)    NOT NULL
        )
    ;

    /* Populate the sample data.
     */
    INSERT INTO @Example
        (
            ID,
            [UID],
            [Version],
            Content
        )
    VALUES
        (1, 1, 1, 'Something'),
        (2, 1, 2, 'Something (changed)'),
        (3, 2, 1, 'Alice has a cat'),
        (4, 2, 2, 'Alice has a cat and a dog'),
        (5, 2, 3, 'Alice has a cat and a canary')
    ;

    /* Return the most recent version of each UID.
     * This is done by using a sub query to calcualte the max of each UIDs version.
     */
    SELECT
        e.*
    FROM
        @Example AS e
            INNER JOIN
                (    
                    /* This sub query finds the max version of
                     * each UID.
                     */
                    SELECT
                        [UID],
                        MAX([Version]) AS Current_Version
                    FROM
                        @Example    
                    GROUP BY
                        [UID]
                ) AS mv
            ON    mv.[UID]            = e.[UID]
            AND    mv.Current_Version    = e.[Version]
    ;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results