Home ยป How to get Ordinal Number in SQL

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 TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 2,381 total views,  2 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.