473,320 Members | 1,896 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL query - highest rated or least voted items

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 ;-)

Jul 23 '05 #1
5 4116
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

<an************@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.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 ;-)

Jul 23 '05 #2
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.

Jul 23 '05 #3
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.

Jul 23 '05 #4
andy (ao***@lycos.co.uk) writes:
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.


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5
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.

Jul 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: YoBro | last post by:
Hi, I have a database that stores in a field the number of views for a product. I want to write a query that gets the two highest views, so I can then output the result. I have this so far,...
0
by: Brian Newsham | last post by:
------=_NextPart_000_004F_01C352B1.E5B8FA20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I'm working on a PHP based website that loads...
2
by: Dom | last post by:
I need to run a query based on a query in Access. The second query has a number of conditions which all work well, but there is one more contition I need to set to make it run properly. the...
3
by: Serious_Practitioner | last post by:
Good day, and thank you in advance for any assistance you can provide. I have a table in an Access 2000 .mdb file, and I've run into something odd and insolvable, at least for me. The database is...
4
by: johnk | last post by:
I have a table of items, with revision numbers. I need to extract the items with highest revision number. The items may be listed several times and I don't know what the highest revision number...
4
by: uspensky | last post by:
I have a table (cars) with 3 fields: VIN, Class, sell_price 101, sports, 10000 102, sports, 11000 103, luxury, 9000 104, sports, 11000 105, sports, 11000 106, luxury, 5000 107, sports, 11000
5
by: Wired Hosting News | last post by:
I tried to be breif and give a scenario so as not to be overlooked because it was soooo long. Let me give you real world. I am a manufacturer of goods and produce 11 items that are distributed...
2
by: fperri | last post by:
Hello, I have a query that creates a table of duplicates in my database. For one set of duplicates (three records), all the fields & values are the same except for the values in one field. This...
21
by: Tarscher | last post by:
Hi all, I have events containing attendees (events has many attendees). The attendee table tells whether a user will attend the event or not. I want to build a query that returns all the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.