This can be achieved through "CASE .. WHEN.." like following
ORDER BY CASE WHEN Result='Gold' THEN 1 WHEN Result='Silver' THEN 2 ELSE 3 END,CategoryId,WeightFrom
For example :
SELECT * FROM
(
SELECT 1 ResultId,1 CategoryId,20.0 WeightFrom,'Ashu' PlayerName,'Silver' Result
UNION
SELECT 2, 1, 20.0, 'Pawan', 'Gold'
UNION
SELECT 3, 1, 22, 'Mahesh', 'Bronze'
UNION
SELECT 4, 1, 23, 'Ram', 'Bronze'
UNION
SELECT 5, 2, 23.00, 'Rajesh', 'Bronze'
UNION
SELECT 6, 2, 22.00, 'Rakesh', 'Gold'
UNION
SELECT 7, 2, 20.00, 'Vikas', 'Silver'
UNION
SELECT 8, 2, 21, 'Suraj', 'Bronze'
) AS T
ORDER BY CASE WHEN Result='Gold' THEN 1 WHEN Result='Silver' THEN 2 ELSE 3 END,CategoryId,WeightFrom
Result will be like:
ResultId CategoryId WeightFrom PlayerName Result
2 1 20.00 Pawan Gold
6 2 22.00 Rakesh Gold
1 1 20.00 Ashu Silver
7 2 20.00 Vikas Silver
3 1 22.00 Mahesh Bronze
4 1 23.00 Ram Bronze
8 2 21.00 Suraj Bronze
5 2 23.00 Rajesh Bronze
Now you can filter the data as per your requirement, may be using some ranking functions.
In case of any further assistance is required, please let me know :)