SQL - ROW NUMBER with no Order By

Question

I am creating a row number in my query based on ordering of a Date column.

This is the query used:

SELECT * 
FROM 
    (SELECT 
         *, 
         ROW_NUMBER() OVER (ORDER BY datePunch) AS RowNum 
     FROM 
         Tempdata 
     WHERE
         datePunch IS NOT NULL) AS LogTable 
WHERE 
    LogTable.RowNum BETWEEN 800 
                    AND (SELECT COUNT(*) 
                         FROM Tempdata 
                         WHERE datePunch IS NOT NULL)
    AND datePunch IS NOT NULL

Now I want the row number column but not by ordering the datePunch column. I just want records as they are inserted in the table with no ordering.

Is it possible to do this?

Note :-

I have created a Utility which syncs Client Database. So Database will be different everytime with no guarantee of Identity Column. I am just mapping the Columns & using them in my Query.

Do you have a column which is IDENTITY? If you don't have then its impossible to do. If you have then you can order by IDENTITY column. — Dave94, Aug 10 '17 at 06:34
*I just want records as they are inserted in the table with no ordering* - rows in a relational table never have an implicit, system-given ordering - there's no ordering at all. So if you don't care about ordering, you could use `ORDER BY NEWID()` to get a random ordering and thus numbering. The `ORDER BY` clause in a `ROW_NUMBER` function is **required** (as per docs) — marc_s, Aug 10 '17 at 06:35
@marc_s I just got this trick with ROW_NUMBER https://blog.sqlauthority.com/2015/05/05/sql-server-generating-row-number-without-ordering-any-columns/ — Anup, Aug 10 '17 at 06:39
ROW_NUMBER is a temporary value calculated when the query is run. To persist numbers in a table, see IDENTITY Property and SEQUENCE. --> https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql — Dave94, Aug 10 '17 at 06:42
I am guessing from your nomenclature, but Tempdata suggests that it has temporary data. Can you not include a row number column here, and supply it when inserting the data? I have used (ORDER BY 1) in the past, and it appeared to return data in the order I wanted, but I would not bet anything substantial on it! — Jonathan Willcock, Aug 10 '17 at 06:44
@Anup Without explicit column there is no guarantee to get specific order. Of course you could try to use `ORDER BY 1/0` or `ORDER BY (SELECT 1)` but it could be undeterministic. For example: parallel exectution plan. — Lukasz Szozda, Aug 10 '17 at 06:51
**[No Seatbelt – Expecting Order without ORDER BY](https://blogs.msdn.microsoft.com/conor_cunningham_msft/2008/08/27/no-seatbelt-expecting-order-without-order-by/)** — Lukasz Szozda, Aug 10 '17 at 06:57
@Anup So you didn't read the article. `@@ROWCOUNT` is another number. Same as `ORDER BY 10`. — Lukasz Szozda, Aug 10 '17 at 07:40
@lad2025 I found this simple trick https://stackoverflow.com/a/36156953/3106062. Do you agree with Ordering this way. — Anup, Aug 10 '17 at 09:12
@Anup One simple question, when you have `(0, 'a'), (0, 'b'),(0, 'c')`. Now you have `ORDER BY first_column` but all the values are the same. I hope do you see point why it is not stable. — Lukasz Szozda, Aug 10 '17 at 09:15
@lad2025 I just want it in the way records are inserted in the table. I tested this query with sample database with 1000 records. It was showing correct records. By for more records don't have guarantee. — Anup, Aug 10 '17 at 09:36

score 0 · Answer 1 · answered Aug 10 '17 at 06:52

You appear to be wanting your rows to always be returned in a certain order (I.e. 'The order they are inserted in the table') but this isn't a concept that makes sense. Rows aren't inserted in an order, and how they're indexed and organised internally to sqlserver isn't something the developer controls so I'd suggest you forget about there being any particular notion of rows having a natural order.

If you want a stable ordering of rows, that is guaranteed not to change based on sql server's index maintenance etc then you really need to insert some data into the table that allows you to order it when querying. This could be a create_date, an incrementing integer etc

Some interesting bathroom reading material: https://www.red-gate.com/simple-talk/sql/database-administration/sql-server-storage-internals-101/

SQL - ROW NUMBER with no Order By

1 Answers1