Remove Redundant Data

Question

I want to create queries to remove/extract redundant rows having similar versions. For example 13.0, 13.0.0, 13.0.0.0 are the same for SQL Server. So I want to delete the rows having less refined version of product - 13.0, 13.0.0 - and be left only with versions 13.0.0.0 and 13.0.0.275.

Is there any way to do this using some SQL query?

As versions 13.0, 13, 13.0.0 and 13.0.0.0 are the same in all sense... so I want to keep the most detailed version out of them, that is 13.0.0.0, and delete other versions. But version 13.0.0.725 and 13.0.0.0 are different so I want to keep this record as it is but for particular product say SQL Server.

The output that I want from the graphical example:

SQL Server with 13.0.0.0 version
SQL Server with 13.0.0.275 version
Visual Studio with 13.0.0.0 version
Visual Studio with 13.0.0.275 version

Another example is-- If there are versions like 2, 2.0, 2.1.0, then in this case, the record having version 2 and 2.0 are the same so I want to keep the more detailed version of same version and delete the rest. The record will show having version 2.0 and 2.1.0.

Is there any way to achieve the same using SQL queries?

Answer 1

You can convert the version string to XML and then extract the four parts replacing no value (nulls) with a 0. Put the version back together and compare to your original table.

-- Sample table
declare @T table
(
  Title varchar(20),
  Version varchar(10)
);

-- Sample data
insert into @T(Title, Version) values
('SQL Server', '13'),
('SQL Server', '13.0.0'),
('SQL Server', '13.0.0.0'),
('SQL Server', '13.0.0.175'),
('Visual Studio', '13'),
('Visual Studio', '13.0.0'),
('Visual Studio', '13.0.0.0'),
('Visual Studio', '13.0.0.175');

-- Get the required versions
select *
from @T as T1
where exists (
             select *
             from @T as T2
               -- Convert version to XML
               cross apply (select cast(replace(T2.Version, '.', '<X/>') as xml)) as X(Value)

               -- Extract the values from the XML and build a new version string with 0 instead of null.
               cross apply (select X.Value.value('text()[1]', 'varchar(10)')+'.'+
                                     isnull(X.Value.value('text()[2]', 'varchar(10)'), '0')+'.'+
                                     isnull(X.Value.value('text()[3]', 'varchar(10)'), '0')+'.'+
                                     isnull(X.Value.value('text()[4]', 'varchar(10)'), '0')) as V(Version)
             where  T1.Title = T2.Title and
                    T1.Version = V.Version
             );

-- Or if you want to delete from your original table ...
delete from @T
where not exists (
                 select *
                 from @T as T2
                   -- Convert version to XML
                   cross apply (select cast(replace(T2.Version, '.', '<X/>') as xml)) as X(Value)

                   -- Extract the values from the XML and build a new version string with 0 instead of null.
                   cross apply (select X.Value.value('text()[1]', 'varchar(10)')+'.'+
                                         isnull(X.Value.value('text()[2]', 'varchar(10)'), '0')+'.'+
                                         isnull(X.Value.value('text()[3]', 'varchar(10)'), '0')+'.'+
                                         isnull(X.Value.value('text()[4]', 'varchar(10)'), '0')) as V(Version)
                 where  [@T].Title = T2.Title and
                        [@T].Version = V.Version
                 );

select *
from @T;