dfek dfek - 1 month ago 5
SQL Question

Create a new text column to identify most recent file version

I have a table which tracks file version history and would like to build a query to report on the most recent version of each file.

Current table structure:

fileID fileName fileVersion
1 fileA 1.0.0
2 fileB 1.0.0
3 fileA 1.0.1
4 fileC 1.0.0
5 fileC 1.0.0
6 fileA 1.0.2


What I would like:

fileID fileName fileVersion currentVersion
1 fileA 1.0.0
2 fileB 1.0.0 current
3 fileA 1.0.1
4 fileC 1.0.0
5 fileC 1.0.1 current
6 fileA 1.0.2 current


The older file versions can either be NULL or listed with a different string (old, previous, etc).

I was able to build a sub query to list the current versions using MAX(Version) and was trying to use that in a CASE statement with Exists or In to create the new column but could not get that to work.

Select t.*,
CASE When t.fileID Exists (sub query) Then 'current' Else NULL End As currentVersion
From t


Essentially, I can return all results and I can separately return just the recent versions but am looking for help with how to use the two queries together to create the currentVersion column.

Answer

You could use the window function Row_Number()

Declare @YourTable table (FileID int,fileName varchar(50),fileVersion varchar(50) )
Insert Into @YourTable values
(1,'fileA','1.0.0'),
(2,'fileB','1.0.0'),
(3,'fileA','1.0.1'),
(4,'fileC','1.0.0'),
(5,'fileC','1.0.1'),
(6,'fileA','1.0.2')

Select * 
      ,currentVersion_A =case when Row_Number() over (Partition By FileName Order by FileID desc) = 1 then 'Current' else '' end
      ,currentVersion_B =case when Row_Number() over (Partition By FileName Order by FileVersion desc) = 1 then 'Current' else '' end
      ,currentVersion_C =case when Row_Number() over (Partition By FileName Order by Cast(ParseName(FileVersion,3 ) as Int) desc,Cast(ParseName(FileVersion,2) as Int) desc,Cast(ParseName(FileVersion,1) as Int) desc) = 1 then 'Current' else '' end 
 from @YourTable
 Order by FileID

-- You have 3 options on how to determine Current (you know your data better than we do)
-- Use currentVersion_A IF FileID is an Identity() and each new version is added to the table
-- Use currentVersion_B IF and ONLY IF version will be 0 and 9
-- Use currentVersion_C As suggested by @HABO which is SMARTER provided the pattern is consistent

Returns

FileID      fileName    fileVersion currentVersion
1           fileA       1.0.0   
2           fileB       1.0.0       Current
3           fileA       1.0.1   
4           fileC       1.0.0   
5           fileC       1.0.1       Current
6           fileA       1.0.2       Current
Comments