-
Select from 1 table using max value from another table
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!
-
Subquery In WHERE
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|