1

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?

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
OTUser
  • 3,788
  • 19
  • 69
  • 127
  • 1
    You have to order by something - what column do you want to order by? – Daniel Marcus May 08 '18 at 20:25
  • 1
    order by (select null) – John Cappelletti May 08 '18 at 20:25
  • 4
    "the natural order of the table data". I think you don't understand a fundamental concept in relational databases. Tables are *unordered*. Result sets without an `order by` are *unordered*. Sets have no "natural order". – Gordon Linoff May 08 '18 at 20:26
  • 4
    Nope, not going to happen. By definition a table is an unordered set. The concept of order comes when we select data. Even the "hack" by @JohnCappelletti will not work long term. http://blogs.msdn.com/b/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx – Sean Lange May 08 '18 at 20:26
  • @JohnCappelletti order by (select null) worked, please post it as answer :) – OTUser May 08 '18 at 20:26
  • 1
    Before you think that is a valid way to ensure ordering the link I posted above. It will work for a while but at some point it will no longer work. – Sean Lange May 08 '18 at 20:27

1 Answers1

6

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.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • 1
    The problem with this approach is that the results will not necessarily be the same each time it is run. If you use this approach for things like updates or deletes there is no guarantee the rows affected will be the same rows in the original select. You need to pick column to order it by. If `id` is an identity column then it is your best bet. – Jeremiah Cooper May 08 '18 at 20:33
  • 5
    @JeremiahCooper that's kinda the entire point of my answer... I see I need to make it more clear. Edited. – Zohar Peled May 08 '18 at 20:35