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
  • 1112 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.

B
bregent
Participating Frequently
February 11, 2010

Got it.

SELECT ROW_NUMBER() OVER (ORDER BY mgAVGscore DESC) AS ROWID, RANK() OVER (ORDER BY mgAVGscore DESC) AS mgtop10, mgmovieID, mgAVGscore, mgmovie, mgmoviecert, mgmovierelease, reviewcount FROM dbo.qryMGavg

Appreciate your help.  Thank you.

Nath.


>Got it.

Good. I wasn't quite sure from your explanation why it wasn't displaying #2. I haven't really worked with MSSQL ranking.

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