Getting latest Versioning Data

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 ge开发者_运维知识库t 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.

Try this:

SELECT t1.id, t1.guid, t1.Data
FROM   table t1
JOIN ( SELECT MAX(id) AS id, guid
       FROM table GROUP BY guid ) t2
       ON t1.id = t2.id AND t1.guid = t2.guid