Query to insert row number to paginate results

Question

I try to execute a query which takes all data from a table. I would like to use pagination as their is a constration in the results. So I try to insert pagination in the query like this

What I tried is this

Select ROW_NUMBER() OVER (ORDER BY reputation) as row, *
From users
Where reputation > 1000000 AND  row >= ##StartRow:INT?1##
AND         row    <= ##EndRow:INT?50000##
ORDER BY    row

The error I receive is this:

Invalid column name 'row'. Invalid column name 'row'.

What can I do?

Hi! Can you tag your choice of dbms and preferably your db version as well? I.e. Oracle, MySql, SQL Server, ... — Thailo, Jan 15 '20 at 08:44

jarlh · Accepted Answer · 2020-01-15T08:52:20.193

Wrap your query up in a derived table (i.e. the subquery):

select * from
(
  Select ROW_NUMBER() OVER (ORDER BY reputation) as row, *
  From users
  Where reputation > 1000000
) dt
where   row >= ##StartRow:INT?1##
  AND   row    <= ##EndRow:INT?50000##
ORDER BY    row

Note that row is a reserved word according to the ANSI/ISO SQL standard, so you may need to delimit that column name as "row". (Or change it to something else.)

Query to insert row number to paginate results

1 Answers1