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 from http://www.15seconds.com/issue/070628.htm and 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 -~----------~----~----~----~------~----~------~--~---
