Order by Issue

I am having a small issue with the order by command.

Everytime I run the code below, everything displays in the correct order which was assigned by the order by command:

select *
from [table1]
order by column_a, column_b, column_c

My problem is that I want it to be stored in another table in the order that I specify, so I run the code below which has the same order:

select *
into [new table]
from [table1]
order by column_a, column_b, column_c

My problem is that when I do a select * form the new table that I inserted all the information into in order, it will not be returned in order. Why would that be? My understanding is that it would store the info in the new table by the way I specified on the order by line.

Any help would be appreciated. It is driving me crazy!Data in a recordset has no order unless specified. While you may have specified an order when you inserted the records, there is no guarantee that they will be maintained in that order, or returned in that order.

You can ensure that they are stored in a specific order by placing a clustered index on the columns you want, and MOST of the time queries against the table will return the records in that order, but again there is no guarantee.

You should always issue an ORDER BY clause on all SQL Statements which require data to be returned in a specified order.|||The only time the resultset will be in the order of the clustered index is if you run your SELECT on a single processor machine, or if you set only 1 processor for SQL Server service, or if you use OPTION (MAXDOP 1). In any other case the order of the clustered index does not affect the order of the resultset without ORDER BY.

Order By in a View

Hi everyone,
I'm relatively new to MSSQL.
I was trying to import a view from MS Access to MSSQL that has an order by statement.

And everytime I tried it gave me the following error:
"The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified."

ِAnyone can help?

M.

Quote:

Originally Posted by mmaamoun

Hi everyone,
I'm relatively new to MSSQL.
I was trying to import a view from MS Access to MSSQL that has an order by statement.

And everytime I tried it gave me the following error:
"The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified."

ِAnyone can help?

M.

option 1. place the ORDER BY on the code where you call the view

option 2. include a top 100% on your view. or use a ridiculous large number that will ensure return of all rows|||Excellent, the select top 100 percent ...
worked like magic for me:)

Thanks:)

Quote:

Originally Posted by ck9663

option 1. place the ORDER BY on the code where you call the view

option 2. include a top 100% on your view. or use a ridiculous large number that will ensure return of all rows