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
Msg 4112, Level 15, State 1, Line 1
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
Reference: Pinal Dave (https://blog.sqlauthority.com)
That was such a simple and elegant solution!! Wish I had thought of it. Hats off!!
Glad you liked it.
This just helped me quite a bit. Thanks!
What happens if there’s the same name twice in your list? Can you make it that it’s the same sequence number?
Yes, you can. Use Dense_rank instead of Row_count.
Thanks Pinal – Just what I needed. Simple and elegant.
This helped me, worked like a charm!