Apply Custom Order In A Given Order Except Ascending And Descending In MSSQL Server


In this article we will learn how we can display records in a custom given order except ascending & descending in MSSQL Server.

Step 1

Let’s create a sample table in MS SQL Server. 

  1. CREATE TABLE [dbo].[TBL_Country](  
  2.     [id] [int] IDENTITY(1,1) NOT NULL,  
  3.     [iso] [char](2) NOT NULL,  
  4.     [name] [varchar](80) NOT NULL,  
  5.     [nicename] [varchar](80) NOT NULL,  
  6.     [iso3] [char](3) NULL,  
  7.     [numcode] [intNULL,  
  8.     [phonecode] [intNOT NULL,  
  10. (  
  11.     [id] ASC  
  12. )  
  13. ON [PRIMARY]  
  15. INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'TH', N'THAILAND', N'Thailand', N'THA', 764, 66)  
  16. INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'GB', N'UNITED KINGDOM', N'United Kingdom', N'GBR', 826, 44)  
  17. INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'BD', N'BANGLADESH', N'Bangladesh', N'BGD', 50, 880)  
  18. INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'AU', N'AUSTRALIA', N'Australia', N'AUS', 36, 61)  
  19. INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'CN', N'CHINA', N'China', N'CHN', 156, 86)  
  20. INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'DK', N'DENMARK', N'Denmark', N'DNK', 208, 45)  
  21. INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'EG', N'EGYPT', N'Egypt', N'EGY', 818, 20)  
  22. INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'FR', N'FRANCE', N'France', N'FRA', 250, 33)  
  23. INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'DE', N'GERMANY', N'Germany', N'DEU', 276, 49)  
  24. INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'GH', N'GHANA', N'Ghana', N'GHA', 288, 233)  
  25. INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'IN', N'INDIA', N'India', N'IND', 356, 91)  
  26. INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'PK', N'PAKISTAN', N'Pakistan', N'PAK', 586, 92)  

Step 3

Run the below sql query. 


SQL Server


The requirement is displayed in the below order.

SQL Server


Let’s run order by sql query on “TBL_Country”.


SQL Server



SQL Server


In both of the above cases the problem was not solved.

Let’s try to attempt this one more time.


SQL Server


Now we have developed the sql query to get custom order result of country in given order.

  1. SELECT * FROM TBL_Country   
  2.               ORDER BY CASE WHEN name = 'INDIA' THEN 1  
  3.               WHEN name = 'PAKISTAN' THEN 2  
  4.               WHEN name = 'CHINA' THEN 3  
  5.               WHEN name = 'GERMANY' THEN 4  
  6.               WHEN name = 'FRANCE' THEN 5  
  7.               ELSE (ROW_NUMBER() OVER (ORDER BY name)+5) END ASC  

Congratulations, you have successfully created a sql query to get custom order result of countries in a given order. If you have any query  or concern just do let me know or just put it in the comment box and I will respond as soon as possible. I am open to discussing anything, even silly questions as well. If you have any suggestions related to this article, please let me know. I promise I will improve this article to a  maximum level. 

That's all for this tutorial.


In this article we have learned how to display rows in a custom given order except Ascending & Descending in MSSQL Server.