CODEWITHSUNDEEP
sql-server
Jake Petroules
Jake Petroules

Reputation: 24170

Getting the ranking of a photo in SQL

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

Upvotes: 0

Views: 58

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166486

Have a look at something like (RANK (Transact-SQL) and maybe DENSE_RANK (Transact-SQL))

SELECT  p.PhotoID,
        ISNULL(v.CountVotes,0) CountVotes,
        RANK() OVER(ORDER BY ISNULL(v.CountVotes,0) DESC) RankInCategory,
        RANK() OVER(PARTITION BY p.CategoryID ORDER BY ISNULL(v.CountVotes,0) DESC) RankInCategory
FROM    Photos p LEFT JOIN 
        (
            SELECT  PhotoID,
                    COUNT(PhotoID) CountVotes
            FROM    Votes v 
            GROUP BY    PhotoID
        ) v ON  v.PhotoID = p.PhotoID

Upvotes: 2

Related Questions