Get the latest version data

advertisements

I have a table which does versioning with an ID and a GUID. The ID increases, but the GUID is static allowing for versioning control.

For example:

ID     GUID        Data
01     ASD         Something
02     ASD         Something Changed
03     ASD         Something Changed Again
04     DSA         Something Else
05     DSA         Something Else Changed

So the question is, how do you get the latest data, so you end up with:

03     ASD         Something Changed Again
05     DSA         Something Else Changed


You could also try

DECLARE @Table TABLE(
        ID INT,
        GUID VARCHAR(10),
        Data VARCHAR(100)
)

INSERT INTO @Table SELECT 01,'ASD','Something'
INSERT INTO @Table SELECT 02,'ASD','Something Changed'
INSERT INTO @Table SELECT 03,'ASD','Something Changed Again'
INSERT INTO @Table SELECT 04,'DSA','Something Else'
INSERT INTO @Table SELECT 05,'DSA','Something Else Changed'

SELECT  *
FROM    @Table t
WHERE   t.ID = ( SELECT MAX(ID) ID
              FROM @Table
              WHERE GUID = t.GUID GROUP BY GUID)

You should probably try to performance check a couple of solutions ( such as the one provided by @Rubens Farias) , and see what works best for you given the indexes you have.