Skip to main content
IrishNJ
IrishNJ
Inspiring
February 5, 2010
Answered

How to create a Top 10 based on values from a database?

  • February 5, 2010
  • 1 reply
  • 1115 views

I have a database table that stores scores for events, like a rating system.

Users choose an event they want to score, enter their score/rating and insert this into the database table.

This gives me, in my table:

Event - Rating - User

Soccer - 5 - Joe Public

Soccer - 4.5 - Jane Public

Tennis - 3 - Joe Public

...and so on.

I would like to do two things.  Firstly, I need to calculate the average for each event, which is easy enough, although I haven't quite got my head around the GROUP BY clause which, as I understand it, only allows me to use one database field?

Secondly, I would like to display, dynamically from the database, a list of top 10 events based on the results of the average ratings.

How easy is this to achieve?  I'm using ASP/VBScript and MSSQL database.

Appreciate any advice offered.  Thanks.
Regards
Nath.

This topic has been closed for replies.
Correct answer bregent

>the GROUP BY clause which, as I understand it,

>only allows me to use one database field?

No. You can include as many values in the group by clause as you want. The output will be one row for each group combination.

>Secondly, I would like to display, dynamically from the database, a list

>of top 10 events based on the results of the average ratings.

In MSSQL you use ranking functionality:

http://www.databasejournal.com/features/mssql/article.php/3661461/New-Ranking-Functions-within-SQL-Server-2005.htm

1 reply

B
bregentCorrect answer
Participating Frequently
February 5, 2010

>the GROUP BY clause which, as I understand it,

>only allows me to use one database field?

No. You can include as many values in the group by clause as you want. The output will be one row for each group combination.

>Secondly, I would like to display, dynamically from the database, a list

>of top 10 events based on the results of the average ratings.

In MSSQL you use ranking functionality:

http://www.databasejournal.com/features/mssql/article.php/3661461/New-Ranking-Functions-within-SQL-Server-2005.htm

IrishNJ
IrishNJAuthor
Inspiring
February 8, 2010

Hi Bregent.  Thank you for your assistance.

I've taken a look at that article, and I see its merits, but what I'm trying to achieve is slightly different in that I need to calculate an average prior to ranking the records.  Can this be done within one SELECT statement, or will I have to create a query view table first, that calculates the average, prior to running the ranking?

Thanks again.

Regards
Nath.

IrishNJ
IrishNJAuthor
Inspiring
February 8, 2010

Hi.  Here is what I've got, so far, to calculate the average:

SELECT     AVG(mgscore) AS mgAVGscore, mgmovieID, mgmovie, mgmovierelease
FROM         dbo.MGreviews
GROUP BY mgmovieID, mgmovie, mgmoviecert, mgmovierelease

How would I then run the RANK function?  Here's the example you provided:

SELECT RANK() OVER (ORDER BY Age) AS [Rank by Age],
       FirstName,
       Age
  FROM Person

I've tried this:

SELECT     RANK () OVER ORDER BY AVG(mgscore) AS mgAVGscore AS [Rank by Score], mgmovieID, mgmovie, mgmovierelease
FROM         dbo.MGreviews
GROUP BY mgmovieID, mgmovie, mgmoviecert, mgmovierelease

...but this produces the following error:

Error in list of function arguments: 'AS' not recognized.  Can you help?  I'm not sure I'm quite understanding how the RANK function works.

Much appreciate.

Regards

Nath.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices