SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table employee (RuleUID varchar(20), id int identity,
                   firstname varchar(20), lastname varchar(20))
insert into employee (RuleUID, firstname, lastname) values 
( '70_rule', 'bill', 'smith'),
( '110_rule', 'mary', 'jones'), 
('120_rule', 'someone', 'else'),
( '80_rule', 'another', 'person'), 
('100_rule', 'jim', 'brown')
5 rows affected

select * from employee


RuleUID
id
firstname
lastname


70_rule
1
bill
smith

110_rule
2
mary
jones

120_rule
3
someone
else

80_rule
4
another
person

100_rule
5
jim
brown


SELECT firstname, lastname, RuleUID
FROM employee
WHERE RuleUID = (SELECT CONCAT(MAX(CAST(SUBSTRING(RuleUID,1,CHARINDEX('_',RuleUID)-1) AS INT)), '_rule') 
                 FROM employee)


firstname
lastname
RuleUID


someone
else
120_rule


SELECT firstname, lastname, RuleUID
FROM employee
WHERE CAST(SUBSTRING(RuleUID,1,CHARINDEX('_',RuleUID)-1) AS INT) =
          (SELECT MAX(CAST(SUBSTRING(RuleUID,1,CHARINDEX('_',RuleUID)-1) AS INT)) 
           FROM employee)


firstname
lastname
RuleUID


someone
else
120_rule


WITH CTE AS (
SELECT firstname, lastname, RuleUID,
       ROW_NUMBER() OVER (ORDER BY CAST(SUBSTRING(RuleUID,1,CHARINDEX('_',RuleUID)-1) AS INT) DESC) AS rn
FROM employee)
SELECT firstname, lastname, RuleUID
FROM CTE
WHERE rn = 1
       


firstname
lastname
RuleUID


someone
else
120_rule


WITH CTE AS (
SELECT firstname, lastname, RuleUID,
       RANK() OVER (ORDER BY CAST(SUBSTRING(RuleUID,1,CHARINDEX('_',RuleUID)-1) AS INT) DESC) AS rn
FROM employee)
SELECT firstname, lastname, RuleUID
FROM CTE
WHERE rn = 1


firstname
lastname
RuleUID


someone
else
120_rule


SELECT TOP 1 firstname
    , lastname
    , RuleUID
FROM employee
ORDER BY CAST(SUBSTRING(RuleUID,1,CHARINDEX('_',RuleUID)-1) AS INT) DESC


firstname
lastname
RuleUID


someone
else
120_rule

RuleUID	id	firstname	lastname
70_rule	1	bill	smith
110_rule	2	mary	jones
120_rule	3	someone	else
80_rule	4	another	person
100_rule	5	jim	brown