Implement custom Order By in SQL Server

sat
 
on Apr 09, 2021 05:03 AM
489 Views

Hi all!

Sql Select statement with Where clause within Order By

I have a gridview with following details:

Name Category Price

N1 B 100

N2 C 105

N3 B 110

N4 A 110

N5 D 105

N6 E 110

N7 A 115

N8 R 120

I want to filter By Category Where it should list items with below custom order:

First all items under "C" category

Then all items under "R" category

Then all items under "P" category

Then all items under "A" category

Then all items under "B" category

Then all items under "D" category

Then all balance items.

Here there is no name under "P" category but it may come later when data will be inserted. My query must yield as per above conditions:

N2 C 105

N8 R 120

N4 A 110

N7 A 115

N1 B 100

N3 B 110

N5 D 105

N6 E 110

Please advise a suitable SQL Select statement. Thanks

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Apr 09, 2021 05:18 AM

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