aspose file tools*
The moose likes JSP and the fly likes RETREIVING MAX (DATE) FROM DATABASE Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » JSP
Bookmark "RETREIVING MAX (DATE) FROM DATABASE" Watch "RETREIVING MAX (DATE) FROM DATABASE" New topic
Author

RETREIVING MAX (DATE) FROM DATABASE

kalyan
Greenhorn

Joined: Jan 22, 2002
Posts: 5
i wanna select the maximum date from the database.
my database is MYSQL and i access my database using JSP.
I tried the following the query
select Max(FIELDNAME) from TABLENAME;
but it's giving the following error
coloumn not found
BUT INFACT THE COLOUMN IS EXISTING IN THE DATABASE AND IF I RETREIVE ALL THE VALUES IN THAT COLOUMN LIKE
SELECT FIELDNAME(THE SAME FIELD AS ABOVE) FROM TABLENAME(THE SAME TABLE AS ABOVE) ITS WORKING FINE
so cud someone suggest me as to how to retrieve the maximum date from the dates already stored in the database thru JSP.
George Larry
Ranch Hand

Joined: Nov 07, 2001
Posts: 52
After running the query- How are you retrieving the results?
I know in Oracle if you do this: SELECT max( blah ) FROM blahs;... Your result set will place the value in a column called 'max( blah )'
So... What I do is this:
query = "SELECT max( blah ) AS maxBlah FROM blahs"
rs = stmt.executeQuery( query );
while ( rs.next() ) {
bigBlah = rs.getString( "maxBlah" );
}
I think there may be a better way of retrieving data if you know your query is only going to return 1 row, but I don't know... My point is- your error might not be in your query, but might be in your retrieval.
Make sense? Hope this helps.
George Larry
Ranch Hand

Joined: Nov 07, 2001
Posts: 52
Sorry... I guess I should have done:
bigBlah = rs.getDate( "maxBlah" );
Since you're getting a date... but the principle is the same.
kalyan
Greenhorn

Joined: Jan 22, 2002
Posts: 5
Thank u very much Mr.George larry
infact after posting this query i have tried and succeeded. and in fact its the same way as u have said in the reply
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16061
    
  21

You might want to tweak your SQL slightly though in order to be database-independent:
SELECT MAX(blah) AS MAXBLAH FROM ...
This gives the results a predictable name instead of whatever the DBMS decides to generate.
Of course, in JDBC, you can avoid the issue by using "bigBlah = rs.getDate(0);" for the case in question. It should be slightly more efficient, since the field name doesn't have to be resolved.
[ January 24, 2002: Message edited by: Tim Holloway ]
[ January 24, 2002: Message edited by: Tim Holloway ]
[ January 24, 2002: Message edited by: Tim Holloway ]

Customer surveys are for companies who didn't pay proper attention to begin with.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: RETREIVING MAX (DATE) FROM DATABASE