CODEWITHSUNDEEP
sql
Kevin G.
Kevin G.

Reputation: 1453

query to find qualified rows in subsets

I have a table of different versions of files. All the versions of a file should be marked deleted except for the last one.

 file_key | path  | version | is_deleted 
----------+-------+---------+------------
        1 | foo/1 |       1 | f       <=== wrong
        2 | foo/1 |       2 | f       <=== wrong
        3 | foo/1 |       3 | f
        4 | bar/2 |       1 | t
        5 | bar/2 |       2 | t
        6 | bar/2 |       3 | f

I'm looking for a query to find those first two rows and mark them is_deleted. I've stumbled around with some self-joins and only ended up with combinatorials, and a "with" CTE was starting to look infinitely recursive.

Is there a sql solution for this, or should I just write a cursor?

Upvotes: 1

Views: 54

Answers (1)

M.Ali
M.Ali

Reputation: 69524

Test Data 

DECLARE @t TABLE (file_key INT, [Path] VARCHAR(10),[Version] INT, is_deleted CHAR(1))
INSERT INTO @t
VALUES 
(1,'foo/1',1,'f'), (2,'foo/1',2,'f'),(3,'foo/1',3,'f'),
(4,'bar/2',1,'t'),(5,'bar/2',2,'t'),(5,'bar/2',3,'f')

Query (For Sql-Server)

;WITH LatestVersions
AS
  (
   SELECT *, rn = RANK() OVER (PARTITION BY [Path] ORDER BY [Version] DESC)
   FROM @t
  )
UPDATE LatestVersions
SET is_deleted =  't'
FROM LatestVersions
WHERE rn > 1

Result Set

╔══════════╦═══════╦═════════╦════════════╗
║ file_key ║ Path  ║ Version ║ is_deleted ║
╠══════════╬═══════╬═════════╬════════════╣
║        1 ║ foo/1 ║       1 ║ t          ║
║        2 ║ foo/1 ║       2 ║ t          ║
║        3 ║ foo/1 ║       3 ║ f          ║
║        4 ║ bar/2 ║       1 ║ t          ║
║        5 ║ bar/2 ║       2 ║ t          ║
║        5 ║ bar/2 ║       3 ║ f          ║
╚══════════╩═══════╩═════════╩════════════╝

Upvotes: 3

Related Questions