Results 1 to 5 of 5
  1. #1
    thelinuxguy is offline Member
    Join Date
    Dec 2008
    Posts
    44
    Rep Power
    0

    Default MySQL/JDBC Mysql query output

    Hi
    I was am trying to get the last 8 entries out of a table in ascending order, so that the last entry in the list was the latest to be updated.
    I currently have:-
    String query = "select dateOut, amountOut from statement, account" + " where account.account_number = statement.account_number and " +
    "account.account_number = ? and dateOut between curdate()-5 and curdate() order by statement_id desc limit 8 ";

    This returns the last 8 records in descending order thereby the last record, the one at the bottom of the list is in fact an older record, whereas the record at the top of the list is the latest. Any ideas as to how I could reverse this, I have tried to use ascending rather than descending as a keyword in the query but this simply gives the first 8 records from the entire table.

    Any help appreciated.

    theLinuxGuy
    Last edited by thelinuxguy; 02-12-2009 at 09:30 PM. Reason: Wanted to add further details to the text

  2. #2
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Java Code:
    "select dateOut, amountOut from (" +
    "select dateOut, amountOut from statement, account " +
    "where account.account_number = statement.account_number " +
    "and account.account_number = ? " +
    "and dateOut between curdate()-5 and curdate() " +
    "order by statement_id desc limit 8)" +
    "order by statement_id"

  3. #3
    thelinuxguy is offline Member
    Join Date
    Dec 2008
    Posts
    44
    Rep Power
    0

    Default

    Hi masijade
    Thank you for responding to me post. I have tried the query that you have supplied and have recieved the following error message.

    com.mysql.jdbc.exceptions.MySQLSyntaxErrorExceptio n: Every derived table must have its own alias

    I tried added an alias to the derived table but it did not work.

    theLinuxGuy

  4. #4
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    How? Like This
    Java Code:
    "select dateOut, amountOut from (" +
    "select dateOut, amountOut from statement, account " +
    "where account.account_number = statement.account_number " +
    "and account.account_number = ? " +
    "and dateOut between curdate()-5 and curdate() " +
    "order by statement_id desc limit 8) subquery" +
    "order by statement_id"
    And what does "it did not work" mean?

    Edit: In any case, this question is better placed on the mysl.org forums, as it is a pure SQL question.

  5. #5
    thelinuxguy is offline Member
    Join Date
    Dec 2008
    Posts
    44
    Rep Power
    0

    Smile MySQL/JDBC select query derived tables !!!PROBLEM SOLVED!!!

    Hi
    Thanks for the post, it was very useful.
    The appropriate syntax for derived tables is as follows:-

    "select m.statement_id, m.dateOut, m.amountOut from " +
    " (select statement.statement_id, statement.dateOut, statement.amountOut from " +
    " statement, account where account.account_number = statement.account_number and "
    + "account.account_number = ? and dateOut between curdate()-5 and curdate() order by " +
    " statement.statement_id desc limit 8) as m order by m.statement_id asc";

    When working with derived tables, the first select statment as shown:
    "select m.statement_id, m.dateOut, m.amountOut from" takes these fields from a derived table. The main query of the actual mysql tables is performed within the query string that is held within the braces (). In order to derive this data, it must be given an alias(a name) such as shown "as m". It is then possible to use the derived table - in this case "m" - within the outer query string which basically takes the results of the initial query and uses the results through a further query.



    Kind Regards

    theLinuxGuy
    Last edited by thelinuxguy; 02-13-2009 at 02:08 AM. Reason: incomplete entry

Similar Threads

  1. Replies: 4
    Last Post: 03-31-2010, 12:08 PM
  2. MySQL/JDBC Prepared Statement Select query
    By thelinuxguy in forum Advanced Java
    Replies: 4
    Last Post: 02-12-2009, 05:29 PM
  3. Mysql/JDBC update query problem
    By thelinuxguy in forum Advanced Java
    Replies: 3
    Last Post: 02-11-2009, 09:56 PM
  4. Java JDBC/MySQL appropriate Syntax
    By thelinuxguy in forum Advanced Java
    Replies: 7
    Last Post: 02-10-2009, 07:57 PM
  5. help ... JDBC or PHP/MySQL
    By bluebarca in forum Advanced Java
    Replies: 1
    Last Post: 11-16-2007, 10:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •