Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Posts
    7

    Unanswered: top 5 max values

    hi i want to have a query that will return the largest 5 ratings from a table

    here is my attempt

    "Select top 5 storyname, genre, storyid, max(numofRatings / totalrating), author, created from Story"

    ....any help much appreciated....

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Re: top 5 max values

    Originally posted by totalfreeloader
    hi i want to have a query that will return the largest 5 ratings from a table

    here is my attempt

    "Select top 5 storyname, genre, storyid, max(numofRatings / totalrating), author, created from Story"

    ....any help much appreciated....
    SELECT TOP 5 storyname, genre, storyid, author, numofRatings / totalrating, created
    FROM Story
    ORDER BY numofRatings / totalrating DESC

  3. #3
    Join Date
    Dec 2003
    Posts
    7
    hi,
    thanks for you reply but im getting an error with this code

    "You tried to execute a query that does not include the specified expression 'storyname' as part of an aggregate function."

    ????????

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by totalfreeloader
    hi,
    thanks for you reply but im getting an error with this code

    "You tried to execute a query that does not include the specified expression 'storyname' as part of an aggregate function."

    ????????
    add a group clause. Are you using sum() somewhere?

  5. #5
    Join Date
    Dec 2003
    Posts
    7
    nope no good this is what i tried....and no im not using sum

    "SELECT TOP 5 storyname, genre, storyid, author, numRatings / totalrating, created FROM Story GROUP BY numRatings / totalrating DESC"

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by totalfreeloader
    nope no good this is what i tried....and no im not using sum

    "SELECT TOP 5 storyname, genre, storyid, author, numRatings / totalrating, created FROM Story GROUP BY numRatings / totalrating DESC"
    heh.. I should have been more explicit.

    SELECT TOP 5 storyname, genre, storyid, author, numofRatings / totalrating, created
    FROM Story
    GROUP BY storyname, genre, storyid, author, created
    ORDER BY numofRatings / totalrating DESC

  7. #7
    Join Date
    Dec 2003
    Posts
    7
    thanks but still no go, i dont think you can use those 2 clauses (group by and order by) in a single query though...had the idea of what u wrote but wasnt sure if it could be done so i didnt bother just changed them.....

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by totalfreeloader
    thanks but still no go, i dont think you can use those 2 clauses (group by and order by) in a single query though...had the idea of what u wrote but wasnt sure if it could be done so i didnt bother just changed them.....
    I assure you, you can use them both.

    Group by is necessar if you are including any aggregate functions within the query. They are in no way related. What error are you receiving now? I'm willing to bet it's not the same one.
    Last edited by Teddy; 12-18-03 at 16:58.

  9. #9
    Join Date
    Dec 2003
    Posts
    7

    Unhappy

    ive used them but it still wont work......

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    totalfreeloader, where are the numofRatings and totalrating fields?

    if they are on each story, then you do not need to use GROUP BY

    the answer is in teddy's first post (#2 in this thread)


    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2003
    Posts
    7

    Smile

    YYYYEESSSS at last, you were right...this is the working query

    "SELECT TOP 5 storyname, genre, storyid, author, numRatings, numRatings / totalrating, created FROM Story ORDER BY numRatings / totalrating ASC"

    i didnt have the numRatings field in the select statement on its own, i needed if for the column in the datagrid where its required........
    thanks very much teddy for your time much appreciated....you too r937

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •