Sort with Insert statement

Question

I'm calculating a linear regression in SQL and need to preserve the Sort order in the #regression_data table.

I'm creating a time series regression and the row_number() is creating the count for X values. The regression needs to maintain the proper sort order to function properly.

The following code works properly when I don't insert into a temp table because I have the ability to sort. Even if I use don't create a Temp Table I don't believe I sort within a derived query.

if object_id('tempdb..#regression_data') is not null begin drop table #regression_data end

Select CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as INT) 'x', CAST([Pax] as INT) 'y', *

--into #regression_data
from #TBB_Temp

´ Order by [Depart Year],[Booking Year], [Sorter]

Tag your question with the databse you are using.

Gordon Linoff
– Gordon Linoff

2019-10-21 16:46:37 +00:00
Commented Oct 21, 2019 at 16:46 — Gordon Linoff
– Gordon Linoff, Commented Oct 21, 2019 at 16:46

Gordon Linoff · Accepted Answer · 2019-10-21 17:54:47Z

2

The simplest is to move the sort to the row_number():

select ROW_NUMBER() OVER (ORDER BY [Depart Year], [Booking Year], [Sorter])) as x,
       CAST([Pax] as INT) as y,
       t.*
--into #regression_data
from #TBB_Temp t;

SQL tables represent unordered sets, so the outer order by does very little.

Another method adds an identity column instead:

select identity(int) as x,
       CAST([Pax] as INT) as y,
       t.*
--into #regression_data
from #TBB_Temp t
order by [Depart Year], [Booking Year], [Sorter];

The one exception to order by being meaningless for an insert is that identity respects the ordering.

Note that for both these methods, you have to use order by when querying the table:

select rd.*
from #regression_data rd
order by x;

See above note on tables and unordered sets.

edited Oct 21, 2019 at 17:54

answered Oct 21, 2019 at 16:47

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sort with Insert statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related