This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Before one week ago, we were preparing a few reports of database performance.
We are publishing a list of our servers in the descending order by showing most utilized servers at first and least utilized servers at last.
But on that day, our client requested to display a particular X server information at always top and then display other servers in descending orders.
Below is a very simple solution of this:
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE tbl_Classes ( ClassID INT PRIMARY KEY ,ClassName VARCHAR(10) ) GO INSERT INTO tbl_Classes VALUES (1,'A'),(2,'B'),(3,'C') ,(4,'D'),(5,'E'),(6,'F') ,(7,'G'),(8,'H'),(9,'I') GO |
Select the sample data with simple ORDER BY DESC:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT * FROM tbl_Classes ORDER BY ClassName DESC ClassID ClassName ----------- ---------- 9 I 8 H 7 G 6 F 5 E 4 D 3 C 2 B 1 A |
ORDER BY the data in decending order and keep Class=D in top:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT * FROM tbl_Classes ORDER BY CASE WHEN ClassName = 'D' THEN 1 ELSE 2 END ,ClassName DESC GO ClassID ClassName ----------- ---------- 4 D 9 I 8 H 7 G 6 F 5 E 3 C 2 B 1 A |
ORDER BY the data in decending order and keep Class=D,E in top:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT * FROM tbl_Classes ORDER BY CASE WHEN ClassName IN ('D','E') THEN 1 ELSE 2 END ,ClassName DESC GO ClassID ClassName ----------- ---------- 5 E 4 D 9 I 8 H 7 G 6 F 3 C 2 B 1 A |
Leave a Reply