SQL SERVER – Generating Row Number Without Ordering Any Columns

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

Solarwinds
SELECT *,ROW_NUMBER() OVER (ORDER BY NAMES) AS SNO FROM #TEST

The reasult is

SQL SERVER - Generating Row Number Without Ordering Any Columns orderneeded1
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

SQL SERVER - Generating Row Number Without Ordering Any Columns orderneeded2
In place of SELECT 100, you can use anything like SELECT 1, SELECT ‘A’, SELECT NULL, etc

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Who ALTER’ed My Database? Catch Them Via DDL Trigger
Next Post
SQL Server – Knowing the Use of Deprecated or Discontinued Features

Related Posts

72 Comments. Leave new

Leave a Reply

Menu