Ranking Query (self join)

[alphacooler]

I would like to get a rank (row # of ordered results) of an article based on its average rating in relation to all other articles. I have a ratings table that holds all of the individual votes cast by users (ratingID, articleID, userID, rating). So I need to order the articles by AVG(ratings.rating) and then come up with an individual article's row # to find its ranking.

I know this involves joining the ratings table on itself with a greater than or less than operator, but i'm completely lost when it comes to self-joins. I know this is easy, but there is a small trick to it and i'm clueless.

Thanks!

 

[r937]

may i ask where the output of the query is to be used? in some front-end application? because that's where, by far, it is easiest to assign a row number

r937.com | rudy.ca