sql - Custom Sort (not using ORDER BY) and pagination -

i have moderately complex query (over 1k loc far) search-type web page. result set needs in order. there 7 different criteria used order set. have 2 table variables 1 called @stagetable , @resulttable. run dynamic query search parameters , put results @stagetable. process @stagetable each of 7 order criteria , put each chunk in @resulttable. working expected.

i asked add pagination monster query. went cte row_number() route. however, realized not respecting order in @resulttable. issue i'm running row_number() on clause uses order not allow me keep order crafted in @resulttable. tried different things including adding case block in cte.

the issue applies when user loads page first time, , when scrolling without using criteria search page.

any ideas on how deal this?

when define @resulttable, add identity column:

declare @resulttable table (      resulttableid int not null identity(1, 1),      . . . ); 

when insert table, put other columns in column list:

insert @resulttable (col1, . .. )     select . . . 

the identity column set according order of input. sql server guarantees ordering of inputs (or @ least ids) when using order by insert (see here -- mikael eriksson reference).

when put in final order by, can include resulttableid ordering:

row_number() on (order col1, resulttableid)