MySQL/JDBC Mysql query output

[thelinuxguy]

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

[masijade]

"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"

[thelinuxguy]

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

[masijade]

How? Like This

"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.

[thelinuxguy]

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