CODEWITHSUNDEEP
asp.netsqlsql-serversql-server-2008t-sql
BlueCode
BlueCode

Reputation: 741

How to select top rated photos in the last few days?

Here is my data context:

Photos (ID, Title)
Users (ID, FullName)
Ratings (PhotoID, UserID, Value, Date)

Business rules:

I want to select the top rated photos by day in the last let's say 3 days. So which photo got the best rating today, yesterday and the day before yesterday? I would like to make the number of days variable if it possible. I have to display the last N days only they rated excluding empty days.

I would like to get the photos in a single query/result because I want to bind it to a ListView to display them on a web form.

I've started this way:

DECLARE @days INT = 3

SELECT TOP (@days) ... FROM Ratings
INNER JOIN Photos ON Photos.ID = Ratings.PhotoID
GROUP BY DATEDIFF(day, [Date], CURRENT_TIMESTAMP)
ORDER BY DATEDIFF(day, [Date], CURRENT_TIMESTAMP) DESC

How can I group my groups by PhotoID, order them by SUM(Value) and select the first one from each group? Thank you very much for your help.

Upvotes: 0

Views: 211

Answers (1)

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

SELECT Date, TotalRating, Photos.*
FROM Photos
INNER JOIN 
(SELECT ROW_NUMBER() OVER (ORDER BY Date DESC) AS RowNumber, 
        PhotoID, Date, TotalRating
 FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Date, ORDER BY TotalRating DESC) AS inRowNumber,
              PhotoID, Date, TotalRating
        FROM (SELECT PhotoID, Date, SUM(Value) AS TotalRating
              FROM Photos
              GROUP BY PhotoID, Date
              HAVING SUM(Value) > 0 ) t)
 WHERE inRowNumber = 1) t ON Photos.Id = t.PhotoID
 WHERE RowNumber <= @days

Upvotes: 2

Related Questions