Paginate pages to receive results from tSQL

Question

I try to run a simple query at sede

Here is the query:

SELECT
    ROW_NUMBER() OVER (ORDER BY u.Displayname DESC) AS row,
    u.Id, u.Reputation
FROM        
    Users u
WHERE       
    u.reputation > ##MinimumRep:int?1000##
    AND row >= ##StartRow:INT?1##
    AND row <= ##EndRow:INT?50000##
ORDER BY    
    row

However the error is related to row as it can't recognized. Please could you help me what can I do to fix it?

Answer 1

You'll have to do that row >= and row <= filtering outside of this query (put this query in a subquery and filter in the main query).

Window functions like row_number() OVER are the last steps to run in a sql statement, so by the time that logic is applied, the WHERE clause has long since been executed. Some RDBMS products like Teradata and Snowflake have a QUALIFY clause where you can put those window function filters, but SQL Server 19 which is what is under the hood of dataexplorer does not have that clause, so the subquery is necessary.

Something like the following will likely work:

SELECT *
FROM 
    (
      SELECT
        ROW_NUMBER() OVER (ORDER BY u.Displayname DESC) AS row,
        u.Id, u.Reputation
      FROM        
        Users u
      WHERE       
        u.reputation > ##MinimumRep:int?1000##
    ) dt
WHERE
    row >= ##StartRow:INT?1##
    AND row <= ##EndRow:INT?50000##
ORDER BY  
    row

However, as I look at this query, I'm not sure what you are trying to do here. This is identical to:

SELECT TOP 50000
  u.Id, 
  u.Reputation
FROM        
  Users u
WHERE       
  u.reputation > ##MinimumRep:int?1000##
ORDER BY DisplayName DESC

Which isn't terribly informative.