I discovered a lot of questions about this subject with nice solutions but not one of them really cope with how to proceed when the information is to not be purchased in a single specific way. For example, the next query:
WITH MyCte AS ( select employee_id, RowNum = row_number() OVER ( order by employee_id ) from V_EMPLOYEE ORDER BY Employee_ID ) SELECT employee_id FROM MyCte WHERE RowNum > 0
is effective when the data will be purchased by worker_id. But let's say my data doesn't have any specific order however the row amounts themselves behave as an ID? Let me write a question such as this (using the
Row_Number() function getting no
ORDER BY clause):
WITH MyCte AS ( select employee_id, RowNum = row_number() OVER ( <PRESERVE ORIGINAL ORDER FROM DB> ) from V_EMPLOYEE ORDER BY Employee_ID ) SELECT employee_id FROM MyCte WHERE RowNum > 0
EDIT: Upon Searching, I determined that this isn't really possible. Can some advise a workaround with this?
Just just in case it's helpful to another person. I simply figured it from elsewhere:
WITH MyCte AS ( select employee_id, RowNum = row_number() OVER (ORDER BY (SELECT 0)) from V_EMPLOYEE ORDER BY Employee_ID ) SELECT employee_id FROM MyCte WHERE RowNum > 0
There's no such factor as ORIGINAL ORDER. SQL server cannot guarantee order of rows if you do not specify ORDER BY. You might get lucky and obtain results particularly order, however it may change whenever.
The actual trouble with the approach that you're suggesting is the fact that order within the db isn't guaranteed. It might coincidentally be coming back for your application within the same order all the time, however the SQL Standard guarantees no such order and could change based on version or edition changes. An order of information from the SQL Server isn't guaranteed with no order by clause. This design could be one which simply depends on 'luck.' If the possible variation so as comes with an effect on your implementation, you might want to change it out now before getting too much in to the implementation.
Use this clause:
order by 1
Ok, do this
order by NEWID()