CODEWITHSUNDEEP
sqlt-sqlpaginationdynamic-sql
Hector Sosa Jr
Hector Sosa Jr

Reputation: 4250

Custom Sort (not using ORDER BY) and pagination

I have a moderately complex query (Over 1K LOC so far) for a search-type web page. The result set needs to be in a certain order. There are 7 different criteria that are used to order this set. I have two TABLE variables one called @stageTable and @resultTable. I run a dynamic query with the search parameters and put the results into @stageTable. I then process @stageTable for each of the 7 order criteria and put each chunk in @resultTable. This is working as expected.

I was asked to add pagination to this monster query. I went the CTE with ROW_NUMBER() route. However, I realized that this is not respecting the order that is in @resultTable. The issue I'm running into is that the ROW_NUMBER() OVER clause uses an ORDER BY which will not allow me to keep the order that I so carefully crafted in @resultTable. I tried different things including adding a CASE block in the CTE.

The issue only applies when the user loads the page for the first time, and when they are scrolling without using any criteria from the search page.

Any ideas on how to deal with this?

Upvotes: 0

Views: 201

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

When you define the @ResultTable, add an identity column:

declare @ResultTable table (
     ResultTableId int not null identity(1, 1),
     . . .
);

When you insert into the table, put all the other columns in the column list:

insert into @ResultTable (col1, . .. )
    select . . .

The identity column will be set according to the order of input. SQL Server guarantees the ordering of inputs (or at least their ids) when using an order by with an insert (see here -- Thanks to Mikael Eriksson for this reference).

When you put in your final order by, you can include the ResultTableId for the ordering:

row_number() over (order by col1, ResultTableid)

Upvotes: 3

Related Questions