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.

CREATE TABLE [dbo].[TBL_Country](
[id] [int] IDENTITY(1,1) NOT NULL,
[iso] [char](2) NOT NULL,
[name] [varchar](80) NOT NULL,
[nicename] [varchar](80) NOT NULL,
[iso3] [char](3) NULL,
[numcode] [int] NULL,
[phonecode] [int] NOT NULL,
CONSTRAINT [PK_TBL_Country] PRIMARY KEY CLUSTERED
(
[id] ASC
)
) ON [PRIMARY]
INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'TH', N'THAILAND', N'Thailand', N'THA', 764, 66)
INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'GB', N'UNITED KINGDOM', N'United Kingdom', N'GBR', 826, 44)
INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'BD', N'BANGLADESH', N'Bangladesh', N'BGD', 50, 880)
INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'AU', N'AUSTRALIA', N'Australia', N'AUS', 36, 61)
INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'CN', N'CHINA', N'China', N'CHN', 156, 86)
INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'DK', N'DENMARK', N'Denmark', N'DNK', 208, 45)
INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'EG', N'EGYPT', N'Egypt', N'EGY', 818, 20)
INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'FR', N'FRANCE', N'France', N'FRA', 250, 33)
INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'DE', N'GERMANY', N'Germany', N'DEU', 276, 49)
INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'GH', N'GHANA', N'Ghana', N'GHA', 288, 233)
INSERT [dbo].[TBL_Country] ([iso], [name], [nicename], [iso3], [numcode], [phonecode]) VALUES (N'IN', N'INDIA', N'India', N'IND', 356, 91)
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

Problem

The requirement is displayed in the below order.

Attempt

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

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

Let’s try to attempt this one more time.

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

SELECT * FROM TBL_Country
ORDER BY CASE WHEN name = 'INDIA' THEN 1
WHEN name = 'PAKISTAN' THEN 2
WHEN name = 'CHINA' THEN 3
WHEN name = 'GERMANY' THEN 4
WHEN name = 'FRANCE' THEN 5
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.