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