jQuery in Action, 3rd edition
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
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: http://aspose.com/file-tools
subject: Simple query problem
It's not a secret anymore!