Row_number function is used to generate a
serial number for a given record set. But you need to always use ORDER
BY clause so that the numbers are assigned to the specific order.
Let us create the following dataset
CREATE TABLE #TEST (NAMES VARCHAR(100))
INSERT INTO #TEST
SELECT 'PINAL' UNION ALL
SELECT 'MAHESH' UNION ALL
SELECT 'SUNIL' UNION ALL
SELECT 'ARVIND' UNION ALL
SELECT 'MURUGAN'
Suppose you want to generate row number, you can use the following statement
SELECT *,ROW_NUMBER() OVER (ORDER BY NAMES) AS SNO FROM #TEST
The reasult is
The numbers are assigned based on ascending order of name
But what if you want to generate row numbers in the same order the data are added.
Can you omit the ORDER BY Clause?
SELECT *,ROW_NUMBER() OVER () AS SNO FROM #TEST
The above throws the following error
The function ‘ROW_NUMBER’ must have an OVER clause with ORDER BY.
But there is a way. Just do not ORDER BY any columns, but ORDER BY a literal value as shown below
SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS SNO FROM #TEST
The result is
In place of SELECT 100, you can use anything like SELECT 1, SELECT ‘A’, SELECT NULL, etc
