problem with RANK command in SELECT statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problem with RANK command in SELECT statement

Author

Topic

nathonjones
Starting Member

15 Posts

Posted - 2010-08-05 : 09:05:12

Hi. My apologies if this is the wrong forum. Completely new to a lot of this. I have a movie review website that takes reviews, calculates the average score and lists a top 10.

Here is my SQL view/query table (dbo.qryMGavg) statement:

SELECT AVG(mgscore) AS mgAVGscore, mgmovieID, mgmovie, COUNT(mgmovieID) AS reviewcount
FROM dbo.MGlatestreviews
GROUP BY mgmovieID, mgmovie

Within an ASP web page I have the following SELECT statement:

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

You can check the page at:
www.moviegossip.co.uk/index.asp

Problem I'm having is that "Toy Story 3", for example, has 7 five star reviews and "Rec 2" has 2 five star reviews, yet Rec 2 is somehow listed above Toy Story 3?

Is there a way for me to sort this within my SELECT statement/s?

I would appreciate any advice that could be offered. Thank you.
Regards
Nath.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-05 : 09:22:30

There is no ORDER BY anywhere, the order by in the row_number/rank clauses don't reorder the output. Can't you simply do like this:

SELECT AVG(mgscore) AS mgAVGscore, mgmovieID, mgmovie, COUNT(mgmovieID) AS reviewcount
FROM dbo.MGlatestreviews
GROUP BY mgmovieID, mgmovie
ORDER BY mgAVGscore DESC, reviewcount DESC

?

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-05 : 09:23:23

Place parantheses around your select in ASP and give it an alias name.
So: (select row_number() over ...) as DT

Then you can select from this derived table and use any order by you want.

So:
select * from
(select row_number() over ...) as DT
order by ...

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-05 : 09:24:54

quote:
Originally posted by Lumbago

There is no ORDER BY anywhere, the order by in the row_number/rank clauses don't reorder the output. Can't you simply do like this:

SELECT AVG(mgscore) AS mgAVGscore, mgmovieID, mgmovie, COUNT(mgmovieID) AS reviewcount
FROM dbo.MGlatestreviews
GROUP BY mgmovieID, mgmovie
ORDER BY mgAVGscore DESC, reviewcount DESC

?

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Hi Lumbage,
this is the select inside a view and shouldn't have an order by.

No, you're never too old to Yak'n'Roll if you're too young to die.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-05 : 09:42:31

quote:
this is the select inside a view and shouldn't have an order by.

Poor observation by me, sorry. But I still don't understand why the row_number/rank functions are needed for...doesn't seem like the data is used.

This seems to me like it should do just fine:
SELECT TOP 10 * FROM dbo.qryMGavg ORDER BY mgAVGscore DESC, reviewcount DESC

If not, then webfreds method is the way to go:


SELECT * 
FROM (
  SELECT 
    ROW_NUMBER() OVER (ORDER BY mgAVGscore DESC) AS ROWID, 
    RANK() OVER (ORDER BY mgAVGscore DESC) AS mgtop10, 
    mgmovieID, mgAVGscore, mgmovie, reviewcount 
    FROM dbo.qryMGavg
  ) as dt
WHERE ROWID <= 10
ORDER BY ROWID

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

nathonjones
Starting Member

15 Posts

Posted - 2010-08-05 : 11:31:35

quote:
Originally posted by webfred

Place parantheses around your select in ASP and give it an alias name.
So: (select row_number() over ...) as DT

Then you can select from this derived table and use any order by you want.

So:
select * from
(select row_number() over ...) as DT
order by ...

No, you're never too old to Yak'n'Roll if you're too young to die.

Hi webfred, and Lumbago. Thank you for your assistance.

I've tried the following statement, as advised:

SELECT * FROM (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) as dt WHERE ROWID <= 10 ORDER BY ROWID

...but this is producing even stranger results than before:
http://www.moviegossip.co.uk/indexTEST.asp

Why would a movie with only one 5 star review now appear at the top? I think I might have misunderstood things a little here!

(nothing out of the ordinary for me!)

Hope you can help. Thanks.
Regards
Nath.

nathonjones
Starting Member

15 Posts

Posted - 2010-08-05 : 11:39:56

Sorry, I realsised that I had WHERE reviewcount > 1 in my previous statement! The display is still wrong though. Rec 2 still shows above Toy Story 3.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-05 : 11:44:02

Sorry but indexTEST.asp isn't opening (waiting, waiting, waiting ...).
The best way is to give
table structure,
some sample data,
wanted output in relation to sample data.

I am sure then you will get much better help than now.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-05 : 12:18:54

Now the page is opening but it is not helpful to help you.
Again:
The best way is to give
table structure,
some sample data,
wanted output in relation to sample data.

I am sure then you will get much better help.

No, you're never too old to Yak'n'Roll if you're too young to die.

nathonjones
Starting Member

15 Posts

Posted - 2010-08-05 : 12:32:50

Movie Reviews Table
mgreviewID
mguserID
mgmovieID
mgscore
mgreview
mgreviewdate

Movie Reviews View/Query Table
SELECT dbo.qryMGlatestmovies.mgmovieID, dbo.qryMGlatestmovies.mgmovie, dbo.qryMGlatestmovies.mgcinedvd, dbo.qryMGlatestmovies.mgmoviecert,
dbo.qryMGlatestmovies.mgmovierelease, dbo.qryMGlatestmovies.mgmoviecast, dbo.qryMGlatestmovies.mgmovietrailerID,
dbo.qryMGlatestmovies.mglivemovie, dbo.MGreviews.mgreviewID, dbo.MGreviews.mgscore, dbo.MGreviews.mgreview,
dbo.MGreviews.mglivereview, dbo.MGreviews.mgreviewdate, dbo.MGpeople.mguserID, dbo.MGpeople.mgusername, dbo.MGpeople.mgaccessID,
dbo.MGpeople.mgemail, dbo.MGpeople.mguserdob, dbo.MGpeople.mgusertown, dbo.MGpeople.mguserphoto, dbo.MGpeople.mgpass,
dbo.MGpeople.mgliveid
FROM dbo.qryMGlatestmovies INNER JOIN
dbo.MGreviews ON dbo.qryMGlatestmovies.mgmovieID = dbo.MGreviews.mgmovieID INNER JOIN
dbo.MGpeople ON dbo.MGreviews.mguserID = dbo.MGpeople.mguserID
WHERE (dbo.MGreviews.mglivereview = 1)

Movie Reviews Average View/Query Table
SELECT AVG(mgscore) AS mgAVGscore, mgmovieID, mgmovie, mgmoviecert, mgmovierelease, COUNT(mgmovieID) AS reviewcount
FROM dbo.qryMGlatestreviews
GROUP BY mgmovieID, mgmovie, mgmoviecert, mgmoviecert, mgmovierelease

TOP 10 recordset (on http://www.moviegossip.co.uk/indexTEST.asp)
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 WHERE reviewcount > 1

I want the top 10 to list the most popular movie in order of their average score, descending, but to also take into account the number of reviews so that, for example, if a movie received 7 five star reviews it would appear above a movie that received 2 five star reviews.

I hope that's clear. Sorry for my lack of understanding.
Regards
Nath.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-06 : 01:26:30

As Lumbago posted earlier in this thread:
SELECT TOP 10 * FROM dbo.qryMGavg ORDER BY mgAVGscore DESC, reviewcount DESC

That should be what you want.
I can't see a reason to use ROW_NUMBER() and RANK().

If that is not what you want then you can go on with ignoring us asking for sample data and wanted output in relation to the sample data

No, you're never too old to Yak'n'Roll if you're too young to die.

nathonjones
Starting Member

15 Posts

Posted - 2010-08-24 : 08:24:10

I can only apologise if I have ignored one of your requests. I think I just haven't understood what you have asked for.

I will try again, but if I miss something please do not think that I am personally ignoring a request you've made.

I basically have three main tables:

Movies
A list of movies currently showing in cinemas.

Reviews
A list of reviews submitted by reviewers.

Reviewers
The above mentioned reviewers.

In the Movies table, I would have...
movieID - 1
movie - Toy Story

movieID - 2
movie - Shrek Forever After

In the Reviews table I would have...
reviewID - 1
movieID - 1
reviewerID - 1
score - 5

reviewID - 2
movieID - 1
reviewerID - 2
score - 4.5

reviewID - 3
movieID - 1
reviewerID - 3
score - 5

reviewID - 4
movieID - 2
reviewerID - 1
score - 5

The reviewers table isn't so important in the achievement of what I'm trying to do.

So...I created a query table that would calculate the average score for each movie based on the score submitted by reviewers in the Reviews table.

Here's the query table I created...
SELECT AVG(mgscore) AS mgAVGscore, mgmovieID, mgmovie, COUNT(mgmovieID) AS reviewcount
FROM dbo.MGlatestreviews
GROUP BY mgmovieID, mgmovie

This would give me a table that held the average score and a review count for each movie.

If you look at my sample data, above, you'll see that movieID 2 has an average score of 5 because it's only had one review - that scored 5.

movieID 1 has a lower average because it received a 4.5 review however it did receive two 5 star reviews.

I'd like to work out how to push movieID 1 to the top, in this instance.

I hope someone can help. Thanks.
Nath.

nathonjones
Starting Member

15 Posts

Posted - 2010-08-24 : 16:20:47

Is this making sense to anyone, or have I annoyed everyone with my apparent ignoring of a request for data?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-25 : 02:59:35

Well...what you would normally do in cases like this is to use what is called "weighted average". The calculation is a little more complex and the result would probably be the same for this limited sample data but the concept should give you a better result, since it takes in to account the number of votes as well. Look up the definition here -> http://www.investopedia.com/terms/w/weightedaverage.asp

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-25 : 03:11:14

The logic you present that the movies with the most high scores should be on top doesn't really make any sense at all. Imagine when comparing two movies, one movie has 3 top scores and 15 4's, the other movie has 4 top scores and 21 1's (4 people that thought it was so stupid it was funny or something and the rest basically says it sucks). In this scenario the sucky movie would be on top but that doesn't make any sense since most people hate it...

And another thought; personally I think you should go for scores ranging from 1 to 10. A movie with an average score of 7 would be considered pretty good, while a score of 3.5 which equates to the same would be considered average...

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources