I have a really bad and irritating problem and would love some help.

I have a table which contains:

1) storyratingid - AutoNumber
2) storyrating - Number entered into database by user
3) visitorip - IP of the user who rated the story
4) storyid - ID of the story that was rated
5) storytitle - The title of the story that was rated

Now I'm trying to show a top rated asp page "top.asp" which will show the top rated stories in order of descending ratings, what the average rating is, and how many users rated that story.

So far, I've come up with a way to show stories which have a rating higher than 4 out of 10. Here it is:

<%@ language = vbscript %>
<%
strServerConnection = "DRIVER={Microsoft Access Driver (*.mdb)};

DBQ="&server.mapPath("data.mdb")

set con = server.createObject("ADODB.Connection")
con.open(strServerConnection)

sqlString = "Select storytitle, storyid, storyrating from ratingdb Where storyrating > 4"

set rs = con.execute(sqlString)

do while not rs.eof

    storyid = rs("storyid")
    storytitle = rs("storytitle")
    storyrating = rs("storyrating")
%>
<%=storytitle%> - Rating: <%=storyrating%>/10 by "n" visitors
  <%
    rs.MoveNext
  loop
  rs.Close
%>

But I can't find a way to make sure that the highest rated is displayed first, and nor can this script show the number of users who voted for that story, represented by "n".

HELP!!!!!!!!!!

try this

<%@ language = vbscript %>
<%
strServerConnection = "DRIVER={Microsoft Access Driver (*.mdb)};

DBQ="&server.mapPath("data.mdb")

set con = server.createObject("ADODB.Connection")
con.open(strServerConnection)

sqlString = "Select * from ratingdb Where storyrating > 4 desc"

set rs = con.execute(sqlString)

do while not rs.eof

    storyid = rs("storyid")
    storytitle = rs("storytitle")
    storyrating = rs("storyrating")
%>
<%=storytitle%> - Rating: <%=storyrating%>/10 by "n" visitors
  <%
    rs.MoveNext
  loop
  rs.Close
%>

Yes I also think so  i just forget the order by storyrating

ljamal, you've got the right idea!!!

Now if I could just get the code right...

sqlString= "select storyid, avg(storyrating) as ratingavg from ratingdb group by storyid as view1, "
sqlString= sqlString & "select storyid, count(storyid) as storyidcount from ratingdb group by storyid as view2, "
sqlString= sqlString & "select distinct(storyid) as storyid, storytitle from ratingdb group by storytitle as view3, "
sqlString= sqlString & "select view3.*, view1.ratingavg, view2.storyidcount from view1, view2, view3 where view3.storyid = view1.storyid and view3.storyid=view2.storyid order by ratingavg desc, storyidcount desc"

Am I right?? Could you post the full code??

Okay, okay, I think I got it right. I made the queries, and did all you said. My last query, view3, is:

SELECT DISTINCT (storyid) AS storyid, storytitle
FROM ratingdb
GROUP BY storytitle;

But when I try to run the top.asp file, it shows me this error:

Circular reference caused by alias 'storyid' in query definition's SELECT list

What does that mean?? Then when I try to open that query in the database, it gives me the same error. What am I doing wrong?