I have the following tables:

Photos [ PhotoID, CategoryID, ... ] PK [ PhotoID ]
Categories [ CategoryID, ... ] PK [ CategoryID ]
Votes [ PhotoID, UserID, ... ] PK [ PhotoID, UserID ]

A photo belongs to one category. A category may contain many photos. A user may vote once on any photo. A photo can be voted for by many users.

I want to select the ranks of a photo (by counting how many votes it has) both overall and within the scope of the category that photo belongs to.

The count of SELECT * FROM Votes WHERE PhotoID = @PhotoID being the number of votes a photo has. I want the resulting table to have generated columns for overall rank, and rank within category, so that I may order the results by either.

So for example, the resulting table from the query should look like:

PhotoID    VoteCount      RankOverall     RankInCategory
1          48             1               7
3          45             2               5
19         33             3               1
2          17             4               3
7          9              5               5
...

...you get the idea. How can I achieve this? So far I've got the following query to retrieve the vote counts, but I need to generate the ranks as well:

SELECT     PhotoID, UserID, CategoryID, DateUploaded,
                          (SELECT     COUNT(CommentID) AS Expr1
                            FROM          dbo.Comments
                            WHERE      (PhotoID = dbo.Photos.PhotoID)) AS CommentCount,
                          (SELECT     COUNT(PhotoID) AS Expr1
                            FROM          dbo.PhotoVotes
                            WHERE      (PhotoID = dbo.Photos.PhotoID)) AS VoteCount, Comments
FROM         dbo.Photos