SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Order by rating

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    78
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Order by rating

    I have a table with five fields:

    ITEMS
    id
    title
    text
    rating
    numvotes

    I want to order the items by their rating.

    First approach:

    Code:
    SELECT * FROM items ORDER BY rating DESC LIMIT 10;
    Not very good.

    Second approach:

    Code:
    SELECT * FROM items ORDER BY rating DESC WHERE numvotes > 5 LIMIT 10;
    Better.

    However, I would like it that the following:

    Code:
    rating: 4,3   (out of 5)
    numvotes 150
    would score higher than

    Code:
    rating 4,4
    numvotes 10
    Since it has alot more votes and is thus a better representation.

    How would I be able to do something like that?

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So what's better
    4.2 with 25 votes or 4.3 with 23 votes?

    What you will have to figure out is perhaps a weighted average maybe multiply your rating with your number of votes. only thing 4.2 with 50 votes would then be the same as 2.1 with 100 votes.

    By the way your second approach above will only result in an error since you can't have a where clause after an order by clause.

  3. #3
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    as an aside, you should have two tables 1 with id, title, text the second table with id, rating

    you then won't have duplication of information. you also won't have to update number of votes by hand and update your table, you can just let the database calculate that for you.

  4. #4
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    78
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think it's better if I combine that data in 1 table (I still have the second table, though with IP info and such)

    Why? I get alot more views (SELECTS) than ratings (INSERT&UPDATE) which I think will make it faster if I can do it in 1 query.

    Thanks for your insights.


Bookmarks

Posting Permissions

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