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
(
Col1 Varchar(5)
)
Go
TRUNCATE TABLE [dbo].SequenceData
Go
Insert [dbo].SequenceDataSelect 'A' Union All
Select 'D' Union All
Select 'A' Union All
Select 'C' Union All
Select 'B'
Go
SELECT * FROM [dbo].SequenceData
Go
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"
