Paging and custom-ordering a result

Question

Hello, I want to query a table so that it´s ordered the following way:

1) "entry" 2) "entry#" 3) "entry something" 4) "..entry.."

I did this via Union All and 4 different queries.

But additionally, I want to include paging, so that I can f.ex. receive the row-numbers 1-100, 101-200 etc. I tried Row_Num() Over (Order By) but did not get it done, I think because the Order By-clause destroys my own order.

My query looks something like this (a little bit simplified)

SELECT Keyword FROM Keyword WHERE Keyword LIKE 'Keyword' Union All SELECT * FROM Keyword WHERE Keyword like '%Keyword%' AND NOT LIKE 'Keyword'

and the whole query i tried out:

WITH SearchResult as 
(SELECT * FROM Keyword WHERE Keyword like 'Keyword' Union All SELECT * FROM Keyword WHERE Keyword like '%Keyword%' AND NOT LIKE 'Keyword') 
SELECT * FROM 
(SELECT ROW_NUMBER() OVER (ORDER BY Keyword DESC) AS RowNum, * 
 FROM SearchResult) AS Results 
WHERE (RowNum BETWEEN (1 - 1) * 100 + 1 AND 1 * 100)

Answer 1

Wrap your unioned queries in another one as a derived table and you can use the top clause.

SELECT TOP 100 * FROM (
   SELECT * FROM table where field = 'entry'
   UNION ALL
   SELECT * FROM table where field = 'entry#'
) sortedresults

---

You were on the right track then. Add a defined column to each of your subsets of sorted results and then you can use that to keep the order sorted.

WITH SearchResult AS
  (SELECT *, ROW_NUMBER() OVER (ORDER BY QueryNum) as RowNum FROM
     (SELECT *, 1 as QueryNum FROM KeywordTable WHERE field = 'Keyword'
      UNION ALL
      SELECT *, 2 from KeywordTable WHERE field = 'Keyword#'
      ) SortedResults
  )
SELECT * from SearchResults WHERE RowNum BETWEEN 4 and 10

It is important that you also sort each subquery by something other than keyword so their order stays the same between runs (and as a secondary sort on the row number function). Example: say you have k1, k2, k3, k4, k5 - if you select * where keyword like k% you might get k1, k2, k3, k4, k5 one time and k5, k4, k3, k2, k1 the next (SQL doesn't guarantee return order and it can differ). That will throw off your paging.