Hi sat,
Use Case statement in Order By clause.
Check this test query. Now please take its reference and correct yours.
SQL
CREATE TABLE #Categories (Name CHAR(2), Category CHAR(1), Price INT)
INSERT INTO #Categories VALUES('N1','B',100)
INSERT INTO #Categories VALUES('N2','C',105)
INSERT INTO #Categories VALUES('N3','B',110)
INSERT INTO #Categories VALUES('N4','A',110)
INSERT INTO #Categories VALUES('N5','D',105)
INSERT INTO #Categories VALUES('N6','E',110)
INSERT INTO #Categories VALUES('N7','A',115)
INSERT INTO #Categories VALUES('N8','R',120)
SELECT * FROM #Categories
ORDER BY CASE WHEN Category = 'C' THEN 1
WHEN Category = 'R' THEN 2
WHEN Category = 'P' THEN 3
WHEN Category = 'A' THEN 4
WHEN Category = 'B' THEN 5
WHEN Category = 'D' THEN 6
ELSE 7
END ASC
DROP TABLE #Categories
Output
| Name |
Category |
Price |
| N2 |
C |
105 |
| N8 |
R |
120 |
| N7 |
A |
115 |
| N4 |
A |
110 |
| N3 |
B |
110 |
| N1 |
B |
100 |
| N5 |
D |
105 |
| N6 |
E |
110 |
