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

[IrishNJ]

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.

[bregent · Accepted solution]

>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]

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]

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.

[IrishNJ]

Got it!  I created a query view in MSSQL that calculated the average then, in Dreamweaver, used the RANK function with this query view table to produce the top 10...

Here's the query view (dbo.qryMGavg) used to calculate the average:

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

And this is the SELECT statement in DW to work out the ranking:

SELECT RANK() OVER (ORDER BY mgAVGscore DESC) AS mgtop10, mgmovieID, mgAVGscore, mgmovie, mgmovierelease

FROM dbo.qryMGavg

Thanks for your help.

Regards
Nath.

[bregent]

You could combine this into a single query by using a derived table, but there is nothing wrong with the way you solved it. Glad to hear you got it working.

[IrishNJ]

Hi again Bregent,

I've noticed that the RANK function numbers items that have a joint score as the same, for example if something is joint first then the rank system lists the numbering like this: 1, 1, 3, 4, 5, 6, 7, 8, 9, 10

There are still 10 items, but 2 is missing because two records are ranked equally.  Is there any way to prevent this or, perhaps, still use the RANK function but display the row numbers rather than the rank number?  That way I'd have 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, regardless of whether there were joint entries or not.

Appreciate the help.  Ta.

Nath.

[IrishNJ]

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.

[bregent]

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