Hi all, I'm trying to return the most rated article with the query given below.

ALTER PROCEDURE dbo.Article_GetMostRatedArticle
(
     @CurrentDate datetime
)
AS
SET NOCOUNT ON

SELECT
 *
 FROM Article
 WHERE Article.TotalRating =
        (SELECT MAX(TotalRating)FROM Article) AND Approved = 1 AND Listed = 1 AND ReleaseDate <= @CurrentDate AND ExpireDate > @CurrentDate
But this query gets failed when the article with the max rating gets expired or unpublished. So I need to find someway to first select all the published & unexpired articles, then find the article having max total rating.
Could anyone of you please guide me how do I write a query to select the most rated article within the published & unexpired articles?

tectrix - not sure what the issue is, however, if you try this (not using date as a paramenter but using the inbuilt getdate() function, it may work:

ALTER PROCEDURE [dbo].[Article_GetMostRatedArticle]
AS
SET NOCOUNT ON

SELECT *
FROM Article
WHERE (TotalRating =
                          (SELECT MAX(TotalRating) AS MaxRating
                            FROM Article AS Article_1))
                            AND (Approved = 1) AND (Listed = 1)
                            AND (ReleaseDate <= GETDATE())
                            AND (ExpireDate > GETDATE())

jimi

http://www.originaltalent.com

tectrix - a more flexible solution may be something along these lines tho:
(getting the TOP n rows [or top 1 for that matter])

ALTER PROCEDURE [dbo].[Article_GetMostRatedArticle]
(
    @NumRows int
)
AS
SET NOCOUNT ON

SELECT TOP(@NumRows) * FROM
    (SELECT *, ROW_NUMBER() OVER (ORDER BY TotalRating DESC)
     AS RowNum FROM Article )
     AS AllArticles
     WHERE (Approved = 1) AND (Listed = 1)
                           AND (ReleaseDate <= GETDATE())
                           AND (ExpireDate > GETDATE())
                           AND RowNum BETWEEN 1 AND @NumRows ORDER BY RowNum ASC

jimi

http://www.originaltalent.com

Thanks jimi for your response.
Well, the second solution is working! but thats strange you didn't use MAX function. This query has really made me mad. I've lost the count how many times I tweaked this query (my poor knowledge of writing queries!)
Let me tell you again what limitation does the original query have.

Suppose the article which has maximum total rating = 20 and it is also published & unexpired, this article will be returned as the most rated article. Now suppose for some reason I make this article unpublished or it gets expired, then the sp should return some article as the most rated which has total rating less than 20, say 15 (published & unexpired). But it will not return the article with total rating 15 in this case. It seems it looks for the articles having max total rating, selects them, if they are unpublished or retired, skips them and nothing returns.
btw, thanks for the query, atleast its working in all the known situations uptil now!

What about just doing this:

ALTER PROCEDURE [dbo].[Article_GetMostRatedArticle]
(
    @CurrentDate datetime
)
AS
SET NOCOUNT ON

SELECT TOP 1 Articles.*
From Articles
     WHERE (Approved = 1) AND (Listed = 1)
                           AND (ReleaseDate <= @CurrentDate)
                           AND (ExpireDate > @CurrentDate)
                           ORDER BY Votes DESC

If what you really want is "the most rated article" (meaning, you want to get the article that got the most people to rate it), then this is what you want. You want to deal with Votes, not TotalRating. 2 voters could have given an article 5 stars, make its TotalRating 10. 4 voters could have given a different article 2 stars each, making that one's TotalRating only 8. However, the second one is "more rated" than the first, considering it got more votes.

slatergator, your idea is not either bad, its just a matter of preference. BTW, jimi's query do have some issues which I come to know after playing with it for a few days. I first need to select published & unexpired articles and then select the articles having Max Total Rating out of the first selected records (published & unexpired).

Hello tectrix,

Well, it's not really just preference. You will actually get very different results if you use TotalRating instead of Votes. What is your actual goal? Is it:
1) Get the article that people rated the most times
or
2) Get the highest rated article
or
3) something else?

what's your mood?
http://www.flixgadget.com

I said its a matter of preference wether you want to get the article that people rated the most times OR you want to get the highest rated article.
I want to get the highest rated article.
btw, the slogan of the flixgadget "whats your mood" got mixed up with your question of what type of results I actually want :p~~

Haha, oh ok, your goal is clear now. Then you actually need both Votes and TotalRating. The highest rated article is the article with the most Stars, not TotalRating. So, you will need to sort by number of Stars (not TotalRating), which is a computed field: TotalRating / Votes. This is the actual value that the "beer mug" rating stars use. So if you used my SQL, it would be:

ALTER PROCEDURE [dbo].[Article_GetMostRatedArticle]
(
    @CurrentDate datetime
)
AS
SET NOCOUNT ON

SELECT TOP 1 Articles.*,
(1.0 * Articles.TotalRating / Articles.Votes) as Stars
From Articles
     WHERE (Approved = 1) AND (Listed = 1)
                           AND (ReleaseDate <= @CurrentDate)
                           AND (ExpireDate > @CurrentDate)
                           ORDER BY Stars DESC

I think this is the most elegant way (I can think of) to do this. I included "1.0 * " because it converts your answer to precise decimal values. Either way, you need to look at actual "Stars" in order to get what you want.

==========================
what's your mood?
http://www.flixgadget.com

Well, I didn't think this way. Thanks anyway. I'm gona try this new query and I'll let you know if I face some new issue!
Now your signature is good, separated from the post! :p