Hi Sanjay

I'm still not totally sure of your requirement but try this - it will generate the sort order you're expecting from the given sample data.

DROP TABLE #Temp

CREATE TABLE #Temp (regionId VARCHAR(10), ParentId VARCHAR(10), TextColumn VARCHAR(10))

INSERT INTO #Temp (regionId, ParentId, TextColumn)

SELECT 'a1', '1', 'wwer' UNION ALL

SELECT 'b1', '1', 'yrt' UNION ALL

SELECT 'c1', '1', 'ryrt' UNION ALL

SELECT 'a11', 'a1', 'eryrty' UNION ALL

SELECT 'b11', 'b1', ' nbnvnfth' UNION ALL

SELECT 'c11', 'c1', 'vc' UNION ALL

SELECT 'b111', 'b11', 'vbc' UNION ALL

SELECT 'a111', 'a11', 'cb' UNION ALL

SELECT 'c111', 'c11', 'bc'

SELECT RIGHT('000000000' + regionId, 10), *

FROM #Temp

ORDER BY RIGHT('000000000' + regionId, 10), ParentId