Getting the ranking of a photo in SQL
- by Jake Petroules
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
