Generate sequence number using ROW_NUMBER() without sorting the actual column

As we know about Ranking functions to return a ranking/sequence value for each row in a partition based on a specific/set of column(s). Right ?

Let me define a table and values

If Object_Id('dbo.SequenceData') Is Null

Create Table [dbo].SequenceData
(
Col1 Varchar(5)
)
Go

TRUNCATE TABLE [dbo].SequenceData

Go

Insert [dbo].SequenceData
Select 'A' Union All
Select 'D' Union All
Select 'A' Union All
Select 'C' Union All
Select 'B'
Go

SELECT * FROM [dbo].SequenceData
Go

let me generate a sequence number for the value

SELECT ROW_NUMBER() OVER(),* FROM [dbo].SequenceData

GO

Msg 4112, Level 15, State 1, Line 31

The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.

But, It's not working, since we need ORDER BY clause specified as per the syntax. right ?

So. let me include ORDER BY clause and see...

SELECT ROW_NUMBER() OVER(ORDER BY Col1),* FROM [dbo].SequenceData

GO

No..No..No, My actual column "Col1" value should not be Sorted. What should we do ?

Let us try something like this

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),* FROM [dbo].SequenceData

GO

We have specified a virtual column in ORDER BY clause which will return always NULL. So, The actual data will not be sorted.

We get a sequence for the column value without sorting it - "AS IS"