OK. This is similar to how Oracle handles it too. How about this?
class MSSQL3Adapter(MSSQLAdapter):
def select_limitby(self, sql_s, sql_f, sql_t, sql_w, sql_o, limitby):
if limitby:
(lmin, lmax) = limitby
return 'SELECT %s FROM (SELECT %s ROW_NUMBER() over (order by
id) AS w_row, %s FROM %s%s%s) TMP WHERE w_row BETWEEN %i AND %s;' %
(sql_f,sql_s,sql_f,sql_t,sql_w,sql_o,lmin,lmax)
return 'SELECT %s %s FROM %s%s%s;' % (sql_s,sql_f,sql_t,sql_w,sql_o)
def rowslice(self,rows,minimum=0,maximum=None):
return rows
Can you help test it?
On Tuesday, 4 December 2012 15:42:16 UTC-6, Niphlod wrote:
>
> *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] <javascript:>
> 1 massimo [email protected] <javascript:>
> 2 simone [email protected] <javascript:>
> 2 simone [email protected] <javascript:>*
>
> 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
>
>
--
