CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008-r2row-number
OTUser
OTUser

Reputation: 3848

Select rows using Row_Number() with out ORDER BY ID

I'm trying to select rows between specific row numbers (like 1 to 50 or 51 to 10 etc.).

Below is my table MatTk schema and all of columns allow nulls:

tk_id [varchar(50)] | mat_id [varchar(50)] | ven_id [varchar(50)] | tk_rate [money]
   1023                     104                     2212               120.11

This article Select subset of rows using Row_Number() suggests ORDER BY id but I want to avoid it and show it in the natural order of the table data.

Also check this article SQL Server 2005 ROW_NUMBER() without ORDER BY which suggests inserting into a temp table but it's not an option since the MatTk table has millions of rows.

Is there a way to query rows by row number without order by ID and without creating temp table?

Upvotes: 1

Views: 1079

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

This article Select subset of rows using Row_Number() suggests ORDER BY id but I want to avoid it and show it in the natural order of the table data.

There is no such thing as "the natural order of the table data".
Database tables are unordered by nature.

This means that the rows returning from a select statement without an order by clause are arbitrarily ordered (note that arbitrary is not the same as random). For more information, read Michael J. Swart's Without ORDER BY, You Can’t Depend On the Order of Results

If you don't care about the order of the numbers in the row_number function, you can use 

row_number() over(order by (select null))

Please note, however, that using this will return arbitrary row numbers - meaning they can't be trusted to stay the same each time you run your query.

Upvotes: 6

Related Questions