How to get max Value Unit from field Value Unit based on first value before comma separated?

Transact-SQL
,
2

Maybe:

WITH UnitOrder
AS
(
	SELECT T.partid, T.ValueUnit
		,ROW_NUMBER() OVER (PARTITION BY partid ORDER BY X.FirstValue DESC, T.ValueUnit DESC) AS rn
	FROM #finaltable T
		CROSS APPLY (VALUES(CAST(LEFT(T.ValueUnit, CHARINDEX(',', T.ValueUnit) -1) AS float))) X (FirstValue)
)
SELECT partid, ValueUnit
FROM UnitOrder
WHERE rn = 1;

or, if the first number is always less than 10:

WITH UnitOrder
AS
(
	SELECT T.partid, T.ValueUnit
		,ROW_NUMBER() OVER (PARTITION BY partid ORDER BY T.ValueUnit DESC) AS rn
	FROM #finaltable T
)
SELECT partid, ValueUnit
FROM UnitOrder
WHERE rn = 1;
2 Likes