Select from 1 table using max value from another table

[janicewt]

Hi, I have two simple tables T1 and T2 as follows:

T1 T2
sid results sid name
1 90 1 John
2 100 2 Mary
3 80 3 Peter
4 92 4 Jim

I want to select the name of the student who has the highest score, and I wrote:

SELECT name
FROM T2
WHERE sid =
(
SELECT sid
FROM T1
WHERE results =
(
SELECT MAX(results)
FROM T1
)
)

is there any more efficient way of doing this? Thanks!

[PinkPanther2003]

Yep,

Use a join.

SELECT t2.Name
FROM t2 INNER JOIN t1
ON t2.SID = t1.SID
WHERE t1.Results = (SELECT MAX(Results) as MaxResult FROM t1)

This method should be faster on most RDBMS's.

HTH,

Peter