CODEWITHSUNDEEP
sql
Tim Mullady
Tim Mullady

Reputation: 35

Sort with Insert statement

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]

Upvotes: 1

Views: 169

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

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.

Upvotes: 2

Related Questions