This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
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 Android Security Essentials Live Lessons this week in the Android 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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Simple query problem
 
Similar Threads
org.hibernate.NonUniqueObjectException
Arrays in Hashtable
Help required(Exceptions)
how can I update xml file in java?
incrementing numbers in database when a new object is added