How to arrange rows of a table in ascending order

问题:

I want to create a new table by arranging the rows in ascending order from the existing table. The code I am trying to use is:

SELECT * 
   INTO new_table
FROM existing_table
ORDER BY col1, col2

However new table does not show any ascending arrangement of rows. Can anybody tell me what is wrong with this code ?

回答1:

Rows in a table are unordered, so it doesn't make sense to talk about rows being in order. And, the result set from a query is unordered, unless you use an order by clause.

That said, you can have the same effect of placing rows into a table in an ordered fashion. Here is a method.

select top 0 *
into new_table
from existing_table;

alter table new_table add new_table_id int identity(1, 1);

insert into new_table(<list of columns without new_table_id>)
    SELECT <list of columns without new_table_id>
    INTO new_table
    FROM existing_table
    ORDER BY col1, col2;

The id column is guaranteed to be in the correct order. In practice, it seems that the rows will be inserted in order. Strictly speaking, the insert order is not guaranteed although the values of the id are ordered correctly.

As mentioned in the comment, you can also do:

alter table new_table add new_table_id int identity(1, 1) not null primary key;

You can do this because the table is empty. As a primary key, the data should be inserted in order.

As a note, though. The query:

select *
from new_table;

does not guarantee the ordering of the results. It makes no difference what the insert order is into the table. You cannot depend on the results being in a particular order just because the rows were ordered that way. For instance, in a multi-threaded environment, the results will generally not be in order, either in theory or in practice.

回答2:

The script you quoted will not guarantee the order of data that is extracted from new_table.

You need to split it into two. Always apply the ORDER BY clause in the script that renders the output to whatever client you are using:

SELECT * 
INTO   new_table
FROM   existing_table

SELECT   *
FROM     new_table
ORDER BY col1, 
         col2

回答3:

This works in MSSQL2012 if your new_table has a PRIMARY KEY in addition to col1 and col2.

INSERT INTO new_table
      (col1, col2)
SELECT (col1, col2)
FROM existing_table
ORDER BY col1, col2

回答4:

First insert it into a temp table that has an identity column then the real table eg...

Declare @TempTable as table (
   id int identity (1,1),
   col1 varchar(255) null,
   col2 varchar(255) null
)

Insert into @TempTable 
select *
from existing_table
order by col1, col2

-- select * from @TempTable T  <--- Ive tested up to here and it works

insert into new_table (col1,col2)
select col1, col2
from @TempTable T