Hi,
Can you try like below Query...
Select * Into #AP from (
Select Category='Computer',RATES_MASTER='50000'
union
Select Category='Computer',RATES_MASTER='20000'
union
Select Category='Computer',RATES_MASTER='15000'
union
Select Category='Laptop',RATES_MASTER='15000'
union
Select Category='Laptop',RATES_MASTER='40000'
union
Select Category='Laptop',RATES_MASTER='35000'
union
Select Category='Tab',RATES_MASTER='25000'
union
Select Category='Tab',RATES_MASTER='10000'
union
Select Category='Tab',RATES_MASTER='8000')o
SELECT M.Category, M.RATES_MASTER
FROM #AP M
INNER JOIN (SELECT TOP 100 PERCENT T.Category, T.RATES_MASTER
FROM (Select ROW_NUMBER()OVER (PARTITION BY Category ORDER BY CAST(RATES_MASTER AS INT) DESC) 'Rno', Category,RATES_MASTER
FROM #AP ) T
WHERE T.Rno =1
ORDER BY CAST(RATES_MASTER AS INT) DESC
) S ON S.Category=M.Category
ORDER BY CAST(S.RATES_MASTER AS INT) DESC, CAST(M.RATES_MASTER AS INT) DESC
Drop Table #AP
Regards,
GVPrabu