Post
Ask Question

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

Introduction

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] [int] NULL,  
  8.     [phonecode] [int] NOT NULL,  
  9.  CONSTRAINT [PK_TBL_Country] PRIMARY KEY CLUSTERED   
  10. (  
  11.     [id] ASC  
  12. )  
  13. ) ON [PRIMARY]  
  14.   
  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. 

SELECT * FROM TBL_Country

SQL Server

Problem

The requirement is displayed in the below order.

SQL Server

Attempt

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.

Summary

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


Similar Articles