Hi, Here is the query for retrieving data from Oracle.
query="SELECT DISTINCT * FROM netview WHERE date1= '" + request.getParameter("showdate4") + "' AND onlinetotal=(SELECT MAX(onlinetotal) FROM netview where date1='"+request.getParameter("showdate4")+"') AND boxofficetotal=(SELECT MAX(boxofficetotal) FROM netview WHERE date1='"+request.getParameter("showdate4")+"')";
now..when i run same query in mysql it shows
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'select max(onlinetotal) from netview)' at line 1
can anyone let me know the syntax for MySQL..for this thing. thanks in advance. Chintan
I don't see anything that wouldn't work on MySQL 5 (but I'm not a MySQL expert). What version of MySQL are you using?
If I were doing this on Oracle, I'd right it substantially differently...
Maybe that or something like it will work on MySQL.
By the way, PreparedStatement is more secure, and generally faster on Oracle.
One more thing, you probably know this, but I find the purpose of the query somewhat baffling; it will only produce a result row when the 2 maximums happen to occur in the same row, and no result rows otherwise. Maybe I'm too tired tonight, but I can't see the practical use of that.
Joined: Jun 29, 2005
Hi, The query which i wrote....retrieves the row which has maximum online total and maximum box office total.Basically it will retrieve only the last entered row with maximum of both things. I cant do it in MySQL..i am using MySQL 4.3. Thnx for help. Chintan
Please post a desc of your table with some sample data as it is quite difficult to make an assumption on a syntax error without knowing what the syntax should be.
Looking at you query I would say you are missing a GROUP BY clause with your MAX statement...I mean if you have just one record for that date there is no need for max if there is more you probably need a group by, but I may be wrong as I am lacking some info .
I would suggest using MySQL Query Browser for the online functions and statements help, if you cannot figure it out with its help, post agian with the additional info I requested.
When you are troubleshooting sql statements you should run each statement separately with sample values check the results and then combine them.