Max

[Guest]

The table
Name Test Scrore Test Date
A 99 05/94
A 96 06/99

I want to create a query, pick the max test score-99; When I have two
columns Name & Test score in query, 99(highest) was picked; But when I
include Test Date in the query, both 99 ans 96 was picked. Is there a way I
can pick the highest score and test date?

thanks,

 

[Jason Lepack]

A correlated subquery will work in this situation:

select
a.[Name],
a.[Test Score],
a.[Date]
from
[the Table] as a
where
a.[Test Score] in (
select
max([Test Score])
from
[the Table] as b
where
a.Name = b.Name)

 

[Guest]

That solved my problem, thank you, Jaosn

A correlated subquery will work in this situation:

select
a.[Name],
a.[Test Score],
a.[Date]
from
[the Table] as a
where
a.[Test Score] in (
select
max([Test Score])
from
[the Table] as b
where
a.Name = b.Name)

The table
Name Test Scrore Test Date
A 99 05/94
A 96 06/99

I want to create a query, pick the max test score-99; When I have two
columns Name & Test score in query, 99(highest) was picked; But when I
include Test Date in the query, both 99 ans 96 was picked. Is there a way I
can pick the highest score and test date?

thanks,