• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

RETREIVING MAX (DATE) FROM DATABASE

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 52
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Saloon Keeper
Posts: 27763
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic