Query to filter out the duplicate and pick the latest - Advanced DBMS: SQL-Server

I'm trying to filter out duplicates and just get the latest of the form name based on the the higher number FIRST and then look at the latest date modified:

FormName                                     ModifiedDate

abc123v1-123456                          2018-08-19 00:00:00.000

abc123v1-123456-001                      2018-08-25 15:55:30.000

abc123v1-123456-001                      2018-08-21 00:00:00.000

abc123v1-123456-002                      2018-08-03 11:00:00.000

abc123v1-123456-002                      2018-08-03 10:00:00.000

abc123v1-666666-092                      2018-08-15 00:00:00.000

abc123v1-000000                          2018-08-15 00:00:00.000


Result:

FormName                                     ModifiedDate

abc123v1-123456-002                      2018-08-03 11:00:00.000

abc123v1-666666-092                      2018-08-15 00:00:00.000

abc123v1-000000                          2018-08-15 00:00:00.000
Assuming:


the base portion is always 15 characters
the suffix is always 3 numeric digits
the suffix is never '000' [I'm going to use '000' as a default for names with no suffix]


Setup:

CREATE TABLE forms (name nvarchar(50), moddate datetime);

INSERT INTO forms VALUES
('abc123v1-123456'    ,'2018-08-19 00:00:00.000'),
('abc123v1-123456-001','2018-08-19 00:00:00.000'),
('abc123v1-123456-001','2018-08-21 00:00:00.000'),
('abc123v1-123456-002','2018-08-03 11:00:00.000'),
('abc123v1-123456-002','2018-08-03 10:00:00.000'),
('abc123v1-666666-092','2018-08-15 00:00:00.000'),
('abc123v1-000000'    ,'2018-08-15 00:00:00.000');


Break the name into 2 components, defaulting to '000' if the name does not have a suffix:

select -- strip off first 15 characters as part1
       substring(name, 1,15)        as part1,
       -- strip out positions 17-19; for forms that don't have 17-19 we'll add a default '-000'
       substring(name+'-000',17, 3) as part2
from   forms
order by 1,2;

part1            part2
===============  =====
abc123v1-000000  000      -- add default '000' for part2
abc123v1-123456  000      -- add default '000' for part2
abc123v1-123456  001
abc123v1-123456  001
abc123v1-123456  002
abc123v1-123456  002
abc123v1-666666  092


At this point we can place the above in a CTE and perform a basic max()\group by operation to find the name with the highest number ... remembering to strip off our default '000' when displaying our results:

with parts as
(
select substring(name, 1,15)        as part1,
       substring(name+'-000',17, 3) as part2
from   forms
)
select part1 +
       -- ignore '000' otherwise append a '-' and the numeric portion
       case when max(part2) = '000' then '' else '-' + max(part2) end  as maxname
from   parts
group by part1
order by 1;

maxname
===================
abc123v1-000000
abc123v1-123456-002
abc123v1-666666-092


And lastly we'll join these max names back to the original table to find the max() modification date, against using a CTE to manage our intermediate max/name logic:

with parts as
(
select substring(name, 1,15)        as part1,
       substring(name+'-000',17, 3) as part2
from   forms
),
first_max as
(
select part1 +
       -- ignore '000' otherwise append a '-' and the numeric portion
       case when max(part2) = '000' then '' else '-' + max(part2) end  as maxname
from   parts
group by part1
)
select fm.maxname     as 'FormName',
       max(f.moddate) as 'ModifiedDate'
from   first_max fm
join   forms f
on     fm.maxname = f.name
group by fm.maxname
order by 1;

FormName            ModifiedDate
=================== ===================
abc123v1-000000     15.08.2018 00:00:00
abc123v1-123456-002 03.08.2018 11:00:00
abc123v1-666666-092 15.08.2018 00:00:00


NOTE: Tweak the order by to get the desired ordering.

NOTE: Dates are displayed in the default format of this particular db fiddle, so your SQL Server instance may display differently, ymmv.