SQL query - highest rated or least voted items

[andreas.muller@gmail.com]

Hello everyone,

I'm trying to solve this problem but can't seem to figure out how to
start. I would like to create a rating system where people can vote
(1-5 stars) on randomly displayed items. The randomly displayed items
should either have very high ratings OR a very low number of ratings.
For example, only return items in the top 20th percentile *OR* items
with fewer than 5 votes.

The question is, how would I write an SQL query to return such a
result? Is it even possible? Should this be handled by my application
rather than the database?

For simplicity, let's assume I have the following table:

tbl_items
-----------------
item_id
item_name
avg_rating
num_votes
-----------------

Any help or pointers in the right direction would be greatly
appreciated. My apologies in advance if the solution is obvious and I
am clearly missing the point ;-)

[John Bell]

Re: SQL query - highest rated or least voted items

Hi

Use TOP to get the highest percentages and a HAVING clause with a count will
give you how many items were added in the SUM. e.g

CREATE TABLE #Scores ( [id] int, score int )

INSERT INTO #Scores ( [id], score )
SELECT 1, 8
UNION ALL SELECT 1, 9
UNION ALL SELECT 1, 10
UNION ALL SELECT 1, 7
UNION ALL SELECT 2, 4
UNION ALL SELECT 2, 6
UNION ALL SELECT 2, 5
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 4, 4
UNION ALL SELECT 4, 4
UNION ALL SELECT 4, 4
UNION ALL SELECT 5, 7
UNION ALL SELECT 5, 7
UNION ALL SELECT 5, 7
UNION ALL SELECT 6, 6
UNION ALL SELECT 6, 6
UNION ALL SELECT 6, 6

-- Not ordered
SELECT [id], SUM(score) AS [Total Score]
FROM #scores
GROUP BY [id]
HAVING COUNT(*) < 5

-- In order highest first
SELECT [id], SUM(score) AS [Total Score]
FROM #scores
GROUP BY [id]
HAVING COUNT(*) < 5
ORDER BY [Total Score] DESC

-- Not ordered therefore don't get highest values
SELECT TOP 40 PERCENT [id], SUM(score) AS [Total Score]
FROM #scores
GROUP BY [id]
HAVING COUNT(*) < 5

-- Ordered so get highest 40 PERCENT
SELECT TOP 40 PERCENT [id], SUM(score) AS [Total Score]
FROM #scores
GROUP BY [id]
HAVING COUNT(*) < 5
ORDER BY [Total Score] DESC

DROP TABLE #Scores

Check out the information in Books online for more details

John

<andreas.muller @gmail.com> wrote in message
news:1116040912 .686078.111690@ f14g2000cwb.goo glegroups.com.. .[color=blue]
> Hello everyone,
>
> I'm trying to solve this problem but can't seem to figure out how to
> start. I would like to create a rating system where people can vote
> (1-5 stars) on randomly displayed items. The randomly displayed items
> should either have very high ratings OR a very low number of ratings.
> For example, only return items in the top 20th percentile *OR* items
> with fewer than 5 votes.
>
> The question is, how would I write an SQL query to return such a
> result? Is it even possible? Should this be handled by my application
> rather than the database?
>
> For simplicity, let's assume I have the following table:
>
> tbl_items
> -----------------
> item_id
> item_name
> avg_rating
> num_votes
> -----------------
>
> Any help or pointers in the right direction would be greatly
> appreciated. My apologies in advance if the solution is obvious and I
> am clearly missing the point ;-)
>[/color]

[andreas.muller@gmail.com]

Re: SQL query - highest rated or least voted items

Thank you John!

This is definitely a push in the right direction. However, if I
understand your query correctly (and after a quick test to confirm),
this returns rows for the highest 40 percent *AND* less than 5
votes/scores. I would need the highest 40 percent *OR* items with less
than 5 votes.

Simply put, I'm trying to find a way for items with fewer than 5 votes
to be part of the result set even if their score is not in the top 40
percent.

[andy]

Re: SQL query - highest rated or least voted items

There are several ways I can think of to do this but here's one.

Two queries.
One returns the top 40%, another less than 5 votes.
Union.

Wrap them with another query and use distinct to de-dupe.

Ordering them in any way could of course be tricky as you're comparing
apples n oranges.

[Erland Sommarskog]

Re: SQL query - highest rated or least voted items

andy (aon14@lycos.co .uk) writes:[color=blue]
> There are several ways I can think of to do this but here's one.
>
> Two queries.
> One returns the top 40%, another less than 5 votes.
> Union.
>
> Wrap them with another query and use distinct to de-dupe.[/color]

No need for a DISTINCT, as UNION implies distinct.

Here is a query drawn from John's repro that mayhe fits:

SELECT id, [Avg Score]
FROM (SELECT TOP 20 PERCENT [id], AVG(score*1.0) AS [Avg Score]
FROM #Scores
GROUP BY id
ORDER BY 2) AS x
UNION
SELECT id, AVG(score*1.0)
FROM #Scores
GROUP BY id
HAVING COUNT(*) < 5


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[andreas.muller@gmail.com]

Re: SQL query - highest rated or least voted items

Thanks guys.

I think you've answered my question. Created a query similar to
Erland's and it does exactly what I need it to. UNION did the trick.