is there a better way? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

is there a better way?

Author

Topic

csgedmonton
Starting Member

1 Post

Posted - 2005-03-10 : 16:32:35

myTable is as follows...

pkey score name color
-----------------------------------
1 3 mike blue
2 5 mike red
3 2 bob red
4 7 joe green
5 6 bob yellow

for each unique name, i want the color with the highest score, ie:

pkey score name color
-----------------------------------
2 5 mike red
4 7 joe green
5 6 bob yellow

i'm using the following query, but think it could be done better.

SELECT * FROM myTable t1
WHERE t1.score = (SELECT MAX(t2.score) from myTable t2 WHERE t2.name=t1.name)

Can this be done without the second select?

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-10 : 16:36:09

No *

* my homage to Rob...gee I hope I'm right

Brett

8-)

PW
Yak Posting Veteran

95 Posts

Posted - 2005-03-10 : 17:03:16

I think (without testing), that this would be more efficient:

SELECT t1.*
FROM myTable t1
INNER JOIN
(
SELECT name, MAX(score) As MaxScore
FROM myTable
GROUP BY name
) dt
On (dt.Name = t1.name and dt.MaxScore = t1.score)

Reason: Aggregates are determined as a set, then joined, rather than resolving row-wise for each row in the outer resultset if structured as a sub-query.

[Edit] I thunk wrong. On running both, the execution plan is identical.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources