CODEWITHSUNDEEP
sqlvb.netms-access-2007
Maadh
Maadh

Reputation: 633

How to Select with Max Value

I have an Access database which contains a Grads table :

Grads
 - studentId 
 - courseId 
 - tryNumber
 - semesterId 
 - finalGrad

How to get all info (studentName, courseName, courseId, finalGrade) from lines with max tryNumber (last try ) ?

Upvotes: 2

Views: 142

Answers (4)

Tirujit Basak
Tirujit Basak

Reputation: 1

select top 1 * from Grads order by tryNumber desc

Upvotes: -1

Taryn
Taryn

Reputation: 247630

If you only want the one row with the max trynumber, then you should be able to use:

select g.studentid,
  g.courseid,
  g.trynumber,
  g.semesterid,
  g.finalgrade
from grads g
inner join
(
  select max(tryNumber) MaxTry
  from grads
) m
  on g.trynumber = m.maxtry

If you want to return each student with the max(tryNumber), then you can use:

select g.studentid,
  g.courseid,
  g.trynumber,
  g.semesterid,
  g.finalgrad
from grads g
inner join
(
  select studentid, max(tryNumber) MaxTry
  from grads
  group by studentid
) m
  on g.trynumber = m.maxtry
  and g.studentid = m.studentid

Upvotes: 4

Ken Clark
Ken Clark

Reputation: 2530

You can use:

Select studentName, courseName, courseId, finalGrade From Grads
Where tryNumber=(Select MAX(tryNumber) From Grads)

Upvotes: 0

Praveen Nambiar
Praveen Nambiar

Reputation: 4892

This should work for you:

SELECT studentName, 
       courseName, couseId, 
       finalGrade, MAX(tryNumber) 
FROM grads
GROUP BY studentName, courseName, couseId, finalGrade

Upvotes: 0

Related Questions