Database Reference
In-Depth Information
| 2 | 2 | 4 |
| 3 | 2 | 4 |
| 4 | 4 | 3 |
| 5 | 5 | 2 |
| 6 | 5 | 2 |
| 7 | 5 | 2 |
| 8 | 8 | 1 |
+------+------+-------+
Ranks are easy to assign within a program as well. For example, the following Ruby
fragment ranks the scores in
t
using the third ranking method:
dbh
.
execute
(
"SELECT score FROM t ORDER BY score DESC"
)
do
|
sth
|
rownum
=
0
rank
=
0
prev_score
=
nil
puts
"Row
\t
Rank
\t
Score
\n
"
sth
.
fetch
do
|
row
|
score
=
row
[
0
]
rownum
+=
1
rank
=
rownum
if
rownum
==
1
||
prev_score
!=
score
prev_score
=
score
puts
"
#{
rownum
}
\t
#{
rank
}
\t
#{
score
}
"
end
end
The third type of ranking is commonly used for sporting events. The following table
contains the American League pitchers who won 15 or more games during the 2001
baseball season:
mysql>
SELECT name, wins FROM al_winner ORDER BY wins DESC, name;
+----------------+------+
| name | wins |
+----------------+------+
| Mulder, Mark | 21 |
| Clemens, Roger | 20 |
| Moyer, Jamie | 20 |
| Garcia, Freddy | 18 |
| Hudson, Tim | 18 |
| Abbott, Paul | 17 |
| Mays, Joe | 17 |
| Mussina, Mike | 17 |
| Sabathia, C.C. | 17 |
| Zito, Barry | 17 |
| Buehrle, Mark | 16 |
| Milton, Eric | 15 |
| Pettitte, Andy | 15 |
| Radke, Brad | 15 |
| Sele, Aaron | 15 |
+----------------+------+
These pitchers can be assigned ranks using the third method as follows:
