• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Simple query problem

 
Nair Anoop
Ranch Hand
Posts: 68
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1510
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Could it be possible that there are multiple rows that meet your criteria?
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic