Results 1 to 8 of 8

Thread: sorting problem

  1. #1
    Join Date
    Jul 2002
    Location
    Oslo, Norway
    Posts
    4

    Exclamation Unanswered: sorting problem

    I'm running an access database with asp on my website:
    http://www.styleboost.com

    I've added a rating system, which counts the number of votes so far (+1 for each vote) and adds up a total rating (current total + (1-5)). This means that there are two columns in my table (rating_sum, rating_votes) and I want to be able to sort by:
    [rating_sum]/[rating_votes] (rating_sum divided by rating_votes).

    How can I do this? Please help me...

    Johan, Norway

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you were so close...
    Code:
    select url, screenshot, title
         , CASE
            WHEN rating_votes = 0 THEN 0  
            ELSE rating_sum / rating_votes 
           END as rating_avg
      from yourtable
    order by rating_avg descending
    this gives unrated entries a rating of 0

    if your database doesn't support CASE, you can also do this with a UNION -- holler if you need it

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Jul 2002
    Location
    Oslo, Norway
    Posts
    4
    thanks ... but it didn't work. At least that's what I think, but I'm no asp guru. ;-) This is what my code looked like before:

    Dim oRS, oCOM
    Set oRS = Server.CreateObject("ADODB.Recordset")
    Set oRS.ActiveConnection = oConn
    oRS.CursorLocation = 3
    oRS.PageSize=10
    oRS.Open "select * from ref_Site order by " & strSort,,3,1

    What should it look like?

    Johan



    Originally posted by r937
    you were so close...
    Code:
    select url, screenshot, title
         , CASE
            WHEN rating_votes = 0 THEN 0  
            ELSE rating_sum / rating_votes 
           END as rating_avg
      from yourtable
    order by rating_avg descending
    this gives unrated entries a rating of 0

    if your database doesn't support CASE, you can also do this with a UNION -- holler if you need it

    rudy
    http://rudy.ca/

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what should it look like? sorry, i don't do asp

    you were asking about sorting, and that's best done in the sql

    i think all you have to do is plug the sql i gave you into your asp code where your current query is, except don't concatenate the strSort variable...

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    Access does not support "case when". Anyway, try the following:

    select field1, field2, ratingsum/ratingvotes from table order by 3

    You can still alias your ratingsum/ratingvotes but you can not use that alias in your order by statement - you need to use the ordinal of the location within the select statement. If you do not alias, use the ratingsum/ratingvotes in your order by statement.
    Last edited by rnealejr; 07-28-02 at 20:24.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Access does not support "case when"
    yeah, i forgot -- access has always had IIF(), which is uglier but it works

    hey, at least i remembered not to divide by zero, and to sort descending
    Code:
    select url, screenshot, title
         , IIF(rating_votes=0, 0,   
               rating_sum / rating_votes)
             as rating_avg
      from yourtable
    order by 3 descending

  7. #7
    Join Date
    Jul 2002
    Location
    Oslo, Norway
    Posts
    4
    Thanks guys. It finally works! It'll be online by the end of the day.

  8. #8
    Join Date
    Jul 2002
    Location
    Oslo, Norway
    Posts
    4
    Thanks guys. It finally works! It'll be online by the end of the day.

Posting Permissions

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