Thank you, I tried this:
db.executesql("""set @num := 0, @type := '';
select type, variety, price
from (
select type, variety, price,
@num := if(@type = type, @num + 1, 1) as row_number,
@type := type as dummy
from fruits
order by type, price
) as x where x.row_number <= 2;""")But it returns a null.
From: Brian M
Sent: Tuesday, March 10, 2015 9:42 PM
To: web2py@googlegroups.com
Subject: [web2py] Re: Running multiple lines of SQL in executesql
Actually just surround your big long multi line SQL statement with triple
quotes and it will work fine. I do that all the time. No need for the \ at each
line break then either. You can declare and set your @variables all within one
executesql query too.
On Tuesday, March 10, 2015 at 2:36:28 PM UTC-5, naveed wrote:
I need to execute multiple lines of SQL in web2py, something like this:
db.executesql("set @num := 0, @type := '';")\
rows = db.executesql("select type, variety, price\
from (\
select type, variety, price,\
@num := if(@type = type, @num + 1, 1) as row_number,\
@type := type as dummy\
from fruits\
order by type, price\
) as x where x.row_number <= 2;")But, when I run it I don't get expected
results in web2py (the variables don't get initialized), even though it works
from the mysql console. What am I doing wrong?
They don't work, even if I put the variable initialization in the same call.
This piece of SQL is from here:
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google
Groups "web2py-users" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/web2py/SQEWZzMQ4Gw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
