April 14, 2004 at 9:08 am
When I select from a table in the order I need and put the results into a temp table with an identity column the identity column is not in the order I need. This happens when I use a create table statement or a "into table" statement
Example 1:
create table #table (indentity,col1,col2,col3)
insert #table(col1,col2,col3)
select col1, col2, col3 from dbo.table order by col1,col2
Select * from #table order by identity returns col1, col2 out of order.
Example 2:
select identity,col1,col2,col3 into #table from dbo.table order by col1,col2.
Select * from #table order by identity returns col1, col2 out of order.
My question is how can I get the identity column to use the same order that I specify in the
cwiney
April 19, 2004 at 8:00 am
This was removed by the editor as SPAM
April 19, 2004 at 9:19 am
If you don't specifically order by some column there is no guarantee that select statements will return results sets ordered by that column. In other words the ONLY way to get the row ordering you want is to use ORDER by listing all the columns you want. ie:
Select * from #table order by identity,col1, col2
You can never assume that SQL chose to physically store rows in the order you inserted them. The only thing you can get the Identiy to do is to start at a certain value and to increment by a certain value. The data will be physically stored in the order that SQL wants. Use ORDER BY to guarantee SELECT order.
Francis
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply