CODEWITHSUNDEEP
sqlms-access
Chris Jones
Chris Jones

Reputation: 672

Rankings using SQL

I am creating a database (schema) in Access for baseball related stats. What I want to for starters is create a query that will rank each Team's standing in a certain stat. For instance, I want to see a ranking for where a team stands relative to the rest in terms of Wins.

For starters, I have a Tables Standings with Columns Teams, and W or

Standings
 Teams     W

I have a query that kind of creates a rank... but it comes out odd

  SELECT Standings.Teams, Standings.W, ((SELECT Count(*) FROM Standings AS Temp 
WHERE Temp.W >= Standings.W ) AS Wins_Rank
    FROM Standings
    ORDER BY Standings.W;

The first few results look like this

Teams    W   Win_Rank
Texas    29    2
St.Louis 29    2
NewYork  28    4  
Cincinnati 28  4
Atlanta   27   7
Pittsburg 27   7
Boston    27   7

I feel like it should be more like 1,1,3,3,5,5,5 shouldn't it? Any help?

Upvotes: 0

Views: 129

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I think you can change it to:

SELECT Standings.Teams, Standings.W,
       (SELECT Count(*)+1
        FROM Standings AS Temp 
        WHERE Temp.W > Standings.W
       ) AS Wins_Rank
FROM Standings
ORDER BY Standings.W;

Notice the change in the inequality as well. Count the number bigger, and then add 1.

Upvotes: 2

Related Questions