Ranking results for Max Selection

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

Transact-SQL (2000)

Ranking results for Max Selection

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-11 : 08:10:10

Lee Moore writes "Consider the following dataset. In trying to find the best score rank per person, I developed the following query, which returns duplicates...

Human Date Score Test Rank
jjones 5/1/1997 930 SAT 12
lmiller 12/1/2003 900 SAT 12
sdand 5/1/1999 930 SAT 12
rdavis 12/1/1993 860 SAT 11
tbaker 1/1/2002 730 SAT 8
lmoore 5/1/1995 890 SAT 11
asmith 6/1/1997 1030 SAT 15
jjones 1/5/1995 13 ACT 6
lmiller 1/1/2000 25 ACT 18
sdand 1/1/2000 15 ACT 8
rdavis 12/1/1990 15 ACT 8
tbaker 4/1/2004 16 ACT 9
lmoore 1/1/2000 9 ACT 2

SELECT A.HUMAN, A.TEST, A.SCORE TEST_TEMP A WHERE A.RANK = (select max(b.score_rank) from test_temp b where b.id = a.id)

Probably elementary, but I am stumped."

SamC
White Water Yakist

3467 Posts

Posted - 2005-10-11 : 09:08:09

Say hello to my leetle friend... (GROUP BY)

SELECT Human, Date, Score, Test, Rank
  FROM MyTable A
  INNER JOIN ( -- Subquery returns records set of Maximum Rank for each carbon-based life form 
    SELECT Human, MAX(Rank) As MaxRank
      FROM MyTable
      GROUP BY Human
  )  B ON B.Human = A.Human
       AND B.MaxRank = A.Rank

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources