*select id, first_name, last_name, email, password, registration_key, reset_password_key, registration_id from ( select ROW_NUMBER() over (order by id) AS total_ordering, id, first_name, last_name, email, password, registration_key, reset_password_key, registration_id from auth_user ) a where total_ordering between 1001 and 2000 * is *more or less* a "limit 1000 offset 1000" (emphasis on "more or less").
1st beware: row_number() needs a "unique column" (needs something that changes at every row) to calculate correctly the "total_ordering" as a sequential integer. If id is not sequential is not a problem, it just has to change for every row. For a table like *name email massimo [email protected] massimo [email protected] simone [email protected] simone [email protected]* a query like *select row_number() over (order by name) as total_ordering, name, email from table* would return *1 massimo [email protected] 1 massimo [email protected] 2 simone [email protected] 2 simone [email protected]* obviously screwing up everything ^_^ (using over(order by name, email) would fix the particular case) Limiting from, e.g., a *group by* set would be a real problem using row_number() for pagination (should include every column to get a distinct "logical primary key") Moreover, for large datasets (or several columns in the "order by" partition) is nowhere near "speedy" (internally it scans the entire columns, put those in order, calculates the "row number") The order of the returned set is depending on the over(order by ) clause (both the "presence" order and the "asc/desc" directives). * select * from ( select row_number() over (order by id) total_ordering, * from auth_user ) a where total_ordering between 1001 and 2000 order by id desc* returns the same set as * select * from ( select row_number() over (order by id) total_ordering, * from auth_user ) a where total_ordering between 1001 and 2000 order by id asc* it's just reverse ordered. the correct translation of "select * from auth_user order by id desc limit 50 offset 50" is * select * from ( select row_number() over (order by id desc) total_ordering, * from auth_user ) a where total_ordering between 51 and 100 * This is "a coincidence": a translation of "select * from auth_user order by first_name limit 50 offset 50" is not possible (without resorting to select top 100 * ... order by first_name and discard the first 50) BTW, MSSQL 2012 adopted the "nicer"* select id, first_name, last_name, email, password, registration_key, reset_password_key, registration_id from auth_user offset 1000 rows fetch next 1000 rows only* It resolves the "syntax" problem, but its slow as hell and memory hungry with large datasets --
