Geting latest Versioning Data

Question

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

Answer 1

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

Answer 2

In Sybase ASE you can:

SELECT
        ID, GUID, Data
FROM
        table
GROUP BY
        GUID
HAVING
        ID = MAX(ID)

Should work in SQL-Server too.

Answer 3

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.