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;
i tried to do this .. Its a different way of doing it ..
if we get into performance tuning .. logical reads .. network round trips .. index seek and index scans and all that ..explain plan ..
then lot of stuff ...
Please let me know if this works for you !!
select a.partid
, valueunit
from
finaltable a
join
(select partid,max(cast(left(valueunit,patindex('%,%',valueunit)-1) as float)) as leftunit from finaltable group by partid) b
on
left(a.valueunit,patindex('%,%',a.valueunit)-1) = b.leftunit