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

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)

,
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

75 Comments. Leave new

  • Hi sit i want to partion my row_number for exampl i hava 2000 row how can bring 1 to 20 and 20 to 40 and so

    Reply
  • it helped me with big problem

    Reply
  • Edgar Mendoza Ortegon
    December 13, 2016 1:38 am

    Muchasa Gracias

    Reply
  • Excellent! Just what I was looking for. Thank you!

    Reply
  • Is it true that the order cannot be guaranteed when using this approach? (i.e. Does that mean if we try to grab the first or last 100 rows – we will never be guaranteed they will be the same rows each time?)

    Reply
  • Brilliant.. saved my day

    Reply
  • You rocks! Thanks

    Reply
  • How could I number rows two by two? For example: have column “A” read 1,1,2,2,3,3,4,4 etc

    Reply
  • Excellent! finally got the solution.. thnx alot!!

    Reply
  • stalinbeltran
    June 18, 2017 11:49 pm

    How do you got to this soluction? What is the “principle” behind this? (By the way, this help me a lot!!)

    Reply
  • Hi Pinal,

    I wanted to use a loop through an actual table not temp table using a ROW_NUMBER(). Is this possible?

    I had a WITH() for the query and then want to traverse, but I think we cannot use a WHILE after the WITH. It has to be a query.

    Can you please help me?

    Uday

    Reply
    • What do you want to do by using LOOP? If you can provide some sample data with expected result, it is easy to answer. There may be simpler method than what you are thinking

      Reply
  • Hi Pinal,

    I want to loop through an actual table, not temp table using ROW_NUMBER(). Is it possible?

    WITH tempwkOrdersData AS
    (
    SELECT ROW_NUMBER() OVER(ORDER BY WORPrimaryId ASC) AS RowNo,
    RowGuId, WORKORDER, WORPrimaryId , WCCPrimaryId , WKRPrimaryId,
    FROM tempwkOrders
    )

    Now I want to have a WHILE loop to iterate into tempwkOrdersData using, RowNo = 1, RowNo = 2 and so on.

    Please help me regarding this.

    Thank you,

    Uday

    Reply
  • Evandro Bellintane
    September 20, 2017 3:39 am

    Perfect! tks

    Reply
  • Nice
    I now used
    alter table #test add RowNr int identity(1,1)
    But this is better
    And by adding names you can also sort on names as well
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 100),Names) AS SNO,* FROM #TEST

    Reply
  • Could you explain why this works?

    Reply
  • I have bookmarked this link. Thank you sharing the method!

    Reply
  • Glauco Guerrero.
    November 8, 2017 1:56 am

    Hi guys,

    I have used this post in some job assignments and it was a great help but I have found a “glitch” when filtering the results and I want to share it. I managed to overcome the issue but I think someone might find it interesting:

    When I issue the SQL query and filter by a column= CONSTANT it will bring me certain order different when filtering the same query with column = @VARIABLE.

    I just made this example.

    select
    row_number() over (ORDER BY (SELECT NULL) ),
    * from master..sysobjects with (nolock) where xtype’U’
    ORDER BY 1

    declare @variable char
    set @variable=’U’
    select
    row_number() over (ORDER BY (SELECT NULL) ),
    * from master..sysobjects with (nolock) where xtype=@variable
    ORDER BY 1

    Reply
  • Given Row number according to record insert in table
    but in table not any date for insert and not any identity column it’s possible ?
    Please give response for that.

    Reply
    • Given Row number according to record inserting time in table
      but in table not any date for insert and not any identity column, it’s possible ?
      Please give response for that.

      Reply
    • Yes possible. Just follow what is suggested in the post

      Reply
  • hi i am using union all with row number but cant get the with where query, here is the query below –

    FROM (SELECT *,
    Row_number() OVER(ORDER BY qtype) as row_num
    FROM (SELECT id as id,
    Pages.page_title as title
    ,’pages’ as qtype
    FROM Pages
    UNION ALL
    SELECT id as id,
    product_name as title
    ,’product’ as qtype
    FROM products) a) b
    WHERE row_num between 1 and 10

    the problem is the unioned table row number is not in sequence even after trying your method (select 100) it return the position of row result of unioned table any help is appreciated,

    thanks

    Reply
  • Thanks for the trick to make this work!

    Reply

Leave a Reply