File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Simple query problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Simple query problem" Watch "Simple query problem" New topic
Author

Simple query problem

Nair Anoop
Ranch Hand

Joined: May 09, 2001
Posts: 68
Hi,
I am using JDBC to connect to an Access table. I have a simple table called:
Student (StudID, FName, LName, Marks)
I would like to find out the student with the highest marks:
SELECT StudID, max(Marks) AS maxMarks FROM Student GROUP by StudId
I get multiple rows returned whereas I need only the highest one.
I am facing a similar problem in other tables too. How can I get all the fields of only the highest row ?
Thanks in advance.
Anoop
Bosun Bello
Ranch Hand

Joined: Nov 06, 2000
Posts: 1510
Could it be possible that there are multiple rows that meet your criteria?


Bosun (SCJP, SCWCD)
So much trouble in the world -- Bob Marley
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

I believe a correlate subquery is what you're after:
"SELECT StudID, FName, LName FROM student where Marks = (SELECT MAX(Marks) FROM students)"
This will return all student information that have a mark equal to the best mark.
Jamie
 
 
subject: Simple query problem