SQLTeam.com | Weblogs | Forums

SQL Query limit, minimum, maximum values syntax?


#1

Hello Dear,
I am new here. I have some issue in my sql query. I am writing sql query in my java code. i am struggling in figuring out the correct syntax of the query. My query is

PreparedStatement statement = con.prepareStatement(""
                 + "Select Transaction_ID, Transaction_Value, Transaction_Type, Description  "
                 + "from Banking "
                 + "limit ? "
                 + "where MIN(Transaction_Value) = ? AND "
                 + "MAX(Transaction_Value) = ? ");

        //setting the limit of records, minimum and maximum values specified by the user
        statement.setInt(1, num);
        statement.setInt(2, min);
        statement.setInt(3, max);
        
        ResultSet result = statement.executeQuery();

When I execute this query, it prompt an error message that
Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MIN(Transaction_Value) = 5 AND MAX(Transaction_Value) = 100' at line 1..
Your help will be really appreciated. I am really very much struggling. I have used simple queries before but dot know how to fix that error.
Thank you so much in advance.


#2

Sorry, this is a Microsoft SQL Server forum, you would probably be better off with a MySQL specialist forum (I believe there is one at www.mysql.com )


#3

First of all, this forum is for Microsoft SQL Server - not MySQL, so you might get better answers in MySQL forum.

I belive the limit line should go after the where section.


#4

I am guessing that it will also need a HAVING clause for the tests on MIN and MAX, rather than using WHERE - or a nested query with sub select. Also going to need a GROUP BY to partition the values for the aggregating functions. But MySQL might handle it differently I suppose ...