Max

E

Eastar

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,
 
J

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)
 
E

Eastar

That solved my problem, thank you, Jaosn

Jason Lepack said:
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,
Click to expand...
Click to expand...
 
Top