Вот наивный однострочный метод LINQ, не содержащий регулярных выражений (заимствованный из python):
List<String> alphaStrings = new List<string>() { "10","2","3","4","50","11","100","a12","b12" };
alphaStrings.OrderBy(g => new Tuple<int, string>(g.ToCharArray().All(char.IsDigit)? int.Parse(g) : int.MaxValue, g)).Dump();
// Order Now: ["2","3","4","10","11","50","100","a12","b12"]
используйте row_number()
with cte as
(SELECT T1.H_ID AS 'ID',T1.NAME,T1.ROLE,T2.SALARY,T3.IMAGE
,row_number() over(partition by T2.img_id order by T3.id) rn
FROM TABLE1 T1
JOIN TABLE2 T2
ON T1.H_ID T2.H_ID
JOIN TABLE3 T3
ON T3.IMG_ID = T2.IMG_ID WHERE T1.STATUS = 'ACTIVE'
) select * from cte where rn=1
После ваших комментариев кажется, что вам нужен подзапрос
select T1.*,T2.sal,a.url
FROM TABLE1 T1
JOIN TABLE2 T2
ON T1.H_ID T2.H_ID
left join ( select min(id),img_id,url from table3 group by img_id,url) a
on T2.IMG_ID= a.img_id
WHERE T1.STATUS = 'ACTIVE'
Я думаю, вы можете просто использовать OUTER APPLY
и TOP 1
для этого
SELECT T1.H_ID AS 'ID',
T1.NAME,
T1.ROLE,
T2.SALARY,
T3.IMAGE
FROM TABLE1 T1
JOIN TABLE2 T2 ON T1.H_ID T2.H_ID
OUTER APPLY(SELECT TOP 1 T3.IMAGE
FROM TABLE3 T3 WHERE T3.IMG_ID = T2.IMG_ID
--ORDER BY <column_name> --to take top 1 value in specific order
) T3
WHERE T1.STATUS = 'ACTIVE'