CODEWITHSUNDEEP
sql
R0b0tn1k
R0b0tn1k

Reputation: 4306

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 get the latest data, so you end up with:

03     ASD         Something Changed Again
05     DSA         Something Else Changed

Upvotes: 0

Views: 130

Answers (2)

Adriaan Stander
Adriaan Stander

Reputation: 166406

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.

Upvotes: 2

Rubens Farias
Rubens Farias

Reputation: 57956

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

Upvotes: 1

Related Questions