MySQL/JDBC Mysql query output
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.
MySQL/JDBC select query derived tables !!!PROBLEM SOLVED!!!
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.