Hi Massimo - They all threw errors. I listed the errors and how I fixed them below. All of this was done directly in SQL SERVER 2005, but no test data.
Thanks for taking a look at this! I hope this info helps... --**************** --STATEMENT 1 --**************** --ERROR --Msg 8120, Level 16, State 1, Line 1 --Column 't1.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. --FIXED SELECT t1.name FROM t1 WHERE t1.id>0 GROUP BY t1.name ORDER BY t1.name; --COMMENTS --Removed t1.id from the SELECT --**************** --STATEMENT 2 --**************** --ERROR --Msg 156, Level 15, State 1, Line 2 --Incorrect syntax near the keyword 'GROUP'. --FIXED SELECT t2_id, t2_t1, t2_name, t1_id, t1_name FROM (SELECT t2.id AS t2_id, t2.t1 AS t2_t1, t2.name AS t2_name, t1.id AS t1_id, t1.name AS t1_name, ROW_NUMBER() OVER(ORDER BY t1.name) AS w_rown FROM t2, t1 WHERE t1.id=t2.t1) AS derived_table WHERE w_rown>=1 AND w_rown<2 GROUP BY t2_id, t2_t1, t2_name, t1_id, t1_name ORDER BY t1_name; --COMMENTS --I don't think you want the GROUP BY in the OVER ()??. --I switched the WHERE clauses for the derived table and the outer query. That provides the join information to the inner query and the outer query selects the set of rows requested --Ambiguous column names come into play since the t1 and t2 table aliases are only available in the derived table. To work around that I had to give each column in the derived table a unique alias. But those would probably need to be converted back to table.column (vs. table_column) before returning the set to the user --Finally each non-aggregate column in the select list has to be in the group by. --**************** --STATEMENT 3 --**************** --ERROR AND COMMENTS SAME AS STATEMENT 2 --FIXED SELECT t1_id, t1_name, t2_id, t2_t1, t2_name FROM (SELECT t1.id AS t1_id, t1.name AS t1_name, t2.id AS t2_id, t2.t1 AS t2_t1, t2.name AS t2_name, ROW_NUMBER() OVER(ORDER BY t1.name) AS w_rown FROM t1 LEFT JOIN t2 ON t1.id=t2.t1 ) AS derived_table WHERE w_rown>=1 AND w_rown<2 GROUP BY t1_id, t1_name, t2_id, t2_t1, t2_name ORDER BY t1_name; On Dec 24, 4:53 am, mdipierro <mdipie...@cs.depaul.edu> wrote: > Making some progress but I need your help. > > Given this: > > db.define_table('t1',SQLField('name')) > db.define_table('t2',SQLField('t1',db.t1),SQLField('name')) > > Are these valid MSSQL Statements? > > 1) db(db.t1.id>0)._select(orderby=db.t1.name,groupby=db.t1.name) > > 'SELECT t1.id, t1.name FROM t1 WHERE t1.id>0 GROUP BY t1.name ORDER BY > t1.name;' > > 2) db(db.t1.id==db.t2.t1)._select > (orderby=db.t1.name,groupby=db.t1.name,limitby=(1,2)) > > 'SELECT t2.id, t2.t1, t2.name, t1.id, t1.name FROM (SELECT t2.id, > t2.t1, t2.name, t1.id, t1.name, ROW_NUMBER() OVER( GROUP BY t1.name > ORDER BY t1.name) AS w_rown FROM t2, t1 WHERE w_rown>=1 AND w_rown<2) > WHERE t1.id=t2.t1 GROUP BY t1.name ORDER BY t1.name;' > > 3) db()._select(db.t1.ALL,db.t2.ALL,left=db.t2.on > (db.t1.id==db.t2.t1),orderby=db.t1.name,groupby=db.t1.name,limitby= > (1,2)) > > 'SELECT t1.id, t1.name, t2.id, t2.t1, t2.name FROM (SELECT t1.id, > t1.name, t2.id, t2.t1, t2.name, ROW_NUMBER() OVER( GROUP BY t1.name > ORDER BY t1.name) AS w_rown FROM t1 LEFT JOIN t2 ON t1.id=t2.t1 WHERE > w_rown>=1 AND w_rown<2) GROUP BY t1.name ORDER BY t1.name;' > > If not, how should they be fixed (SQL)? > > Massimo > > On Dec 23, 4:59 pm, mdipierro <mdipie...@cs.depaul.edu> wrote: > > > 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.htmandrequiresSQL 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 -~----------~----~----~----~------~----~------~--~---