Results 1 to 5 of 5
Thread: MySQL/JDBC Mysql query output
- 02-12-2009, 10:27 PM #1
Member
- Join Date
- Dec 2008
- Posts
- 44
- Rep Power
- 0
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.
theLinuxGuyLast edited by thelinuxguy; 02-12-2009 at 10:30 PM. Reason: Wanted to add further details to the text
- 02-12-2009, 11:33 PM #2
Senior Member
- Join Date
- Jun 2008
- Posts
- 2,568
- Rep Power
- 15
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"
- 02-13-2009, 12:22 AM #3
Member
- Join Date
- Dec 2008
- Posts
- 44
- Rep Power
- 0
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
- 02-13-2009, 01:02 AM #4
Senior Member
- Join Date
- Jun 2008
- Posts
- 2,568
- Rep Power
- 15
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"
Edit: In any case, this question is better placed on the mysl.org forums, as it is a pure SQL question.
- 02-13-2009, 02:57 AM #5
Member
- Join Date
- Dec 2008
- Posts
- 44
- Rep Power
- 0
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
theLinuxGuyLast edited by thelinuxguy; 02-13-2009 at 03:08 AM. Reason: incomplete entry
Similar Threads
-
ClassNotFoundException com.mysql.jdbc.Driver
By Heather in forum JDBCReplies: 4Last Post: 03-31-2010, 01:08 PM -
MySQL/JDBC Prepared Statement Select query
By thelinuxguy in forum Advanced JavaReplies: 4Last Post: 02-12-2009, 06:29 PM -
Mysql/JDBC update query problem
By thelinuxguy in forum Advanced JavaReplies: 3Last Post: 02-11-2009, 10:56 PM -
Java JDBC/MySQL appropriate Syntax
By thelinuxguy in forum Advanced JavaReplies: 7Last Post: 02-10-2009, 08:57 PM -
help ... JDBC or PHP/MySQL
By bluebarca in forum Advanced JavaReplies: 1Last Post: 11-16-2007, 11:05 AM
Bookmarks