I think so. This is what we do with Oracle. Tomorrow. Thank you for the example. It helps.
Massimo On Dec 23, 1:26 pm, Mike <michael.mcgreg...@gmail.com> wrote: > Hi Everyone, > > As the books notes, limitby isn't fully supported by mssql since the > TOP command always starts at index 0. > > So to implement paging I ended up writing my own SQL to mimic the > limitby behavior. The main example I used was > fromhttp://www.15seconds.com/issue/070628.htmand requires SQL 2005's > ROW_NUMBER function. > > My web2py controller snippet: > > #build the SQL query > query = "SELECT " + select_cols + " FROM (SELECT ROW_NUMBER() OVER > (ORDER BY "+sidx+" "+sord+") AS rownum, " + select_cols + " FROM "+ > from_tbl + where_clause > #close the inner query and finish the outer query by using the > rownum() to return only the rows requested by the > user > query = query + ") AS tbl1 WHERE rownum >= "+str(first_rownum)+" > AND rownum < "+str(last_rownum)+" ORDER BY " + sidx + " " + sord > > This produces something to the effect of... > SELECT person, income > FROM (SELECT ROW_NUMBER() OVER(ORDER BY person) AS > rownum, person, income FROM Salaries) AS Salaries1 > WHERE rownum >= 5 AND rownum <= 9 > ORDER BY income > > Note that the row numbers start with 1 (not 0) by default. > > My question is could this method be incorporated into the DAL? I've > peeked at the sourcecode by I must admit it's a bit over my head > still. If it is possible I'm willing to do my best to help with a > patch but would need a fair amount of help. > > Thanks! --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "web2py Web Framework" group. To post to this group, send email to web2py@googlegroups.com To unsubscribe from this group, send email to web2py+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/web2py?hl=en -~----------~----~----~----~------~----~------~--~---
