How to get Ordinal Number in SQL

If any number that is written like as 1st, 2nd, 3rd and so on.. is called an ordinal number, and it is read as 1st( first), 2nd(second), 3rd(third), 4th fourth respectively.




By adding a letter st, nd, rd, and th in the end of any number, makes that number an ordinal number.

Lets create a T-SQL that returns an ordinal number for any specified number.

Following T-SQL query, returns an ordinal number for specified number.

DECLARE @Number AS INT;
SET @Number = 10;
SELECT @Number AS Number, Cast(
  @Number AS VARCHAR(15)) + 
   CASE WHEN @Number % 100 IN (11,12,13) THEN 'th' 
   WHEN @Number % 10 = 1 THEN 'st' 
   WHEN @Number % 10 = 2 THEN 'nd' 
   WHEN @Number % 10 = 3 THEN 'rd' 
   ELSE 'th' 
   END AS OrdinalNumber

You can see the ordinal number for 10 is 10th. Similarly you can get ordinal number of any number that you will specify to variable @Number.

Lets use the same logic with Recursive CTE, and create an ordinal number for 1 to 100 numbers.

WITH CTE_OrdinalNumbers
AS
(
SELECT 1 AS N
UNION ALL
SELECT N + 1
FROM CTE_OrdinalNumbers
WHERE N < 100
)
SELECT N AS Number, CAST(N AS VARCHAR(15)) 
+
  CASE
  WHEN N % 100 IN (11,12,13) THEN 'th' 
  WHEN N % 10 = 1 THEN 'st'
  WHEN N % 10 = 2 THEN 'nd'
  WHEN N % 10 = 3 THEN 'rd'
  ELSE 'th' 
  END AS OrdinalNumber
FROM CTE_OrdinalNumbers
OPTION (MAXRECURSION 0)

…………….| |………………

You can see, an ordinal number for 1 to 100 are returned that is 1st to 100th which can be seen in column OrdinalNumber.

Also Read..

SQL Server Code Snippets Feature in SSMS

SQL Server Template Explorer in SSMS

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More