Hi all, I m still having trouble with this particular query. However, it works fine on other platforms. I'd still like to use Monet for the massive speed improvements, but I just can't figure out why the query is erroring. I have a table that looks like: fid title D0 ------------------------- 1 type1 0.25 2 type2 0.59 3 type1 0.26 ... And the following query works just fine: SELECT fid FROM signatures ti ORDER BY ti.D0 ASC LIMIT 1 OFFSET 0; However when use this query within the WHERE clause of another query, it fails: SELECT fid, title FROM signatures WHERE fid = ( SELECT fid FROM signatures ti ORDER BY ti.D0 ASC LIMIT 1 OFFSET 0 ); With the error: "syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select fid, title from signatures where fid = ( select fid from signatures ti order" Can anyone tell why Monet can't process this, but MySQL does just fine? MS SQL works too as long as I use TOP instead of LIMIT, and keep the offset at 0 (which I would like to eventually change). Any help is greatly appreciated. Cheers, Brett On Mon, Jan 10, 2011 at 4:57 PM, Brett Spurrier <brett.spurrier@gmail.com>wrote:
Hi all,
I have yet another query that I am having trouble running with Monet, and I am hoping a seasoned expert can help me figure out what is going on here.
My query is: SELECT fid, title, D0 AS ed FROM signatures t WHERE fid = ( SELECT fid FROM signatures ti WHERE ti.guid = t.guid ORDER BY D0 ASC LIMIT 1 OFFSET 1) ORDER BY D0 ASC, fid DESC;
but I am getting the error: "!syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select fid, title, d0 as ed from signatures t where fid = ( select fid from signatures ti where ti.guid = t.guid order"
So it appears to not like the ORDER BY term, and I can't figure out why.
I am trying to grab the second element (after ordering by D0) based on distinct GUIDs. The query iteself is based on the answer for a question from stackoverflow that I posted: http://stackoverflow.com/questions/4634068/sql-query-with-order-by-part-2
Can anyone help me figure this out? The syntax looks fine to me. By the way, I have "D0 AS ed" because D0 will eventually be replaced with a simple math operator (i.e., D0/D1).
Many thanks, Brett