File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases 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 Meteor in Action this week in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Simple query problem" Watch "Simple query problem" New topic

Simple query problem

Nair Anoop
Ranch Hand

Joined: May 09, 2001
Posts: 68
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.
Bosun Bello
Ranch Hand

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

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.
I agree. Here's the link:
subject: Simple query problem