Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Making A Best Topics Query

Status
Not open for further replies.

sp0om

Programmer
Jan 20, 2006
2
US
I have a rating system on a forum I'm building. Users can rate a topic either good or bad. For each Good rating, one point is added to the topic's score. For every Bad rating, one point is subtracted to the topic's score.

Each time a rating is made, several things are inputted into a MySQL table called ratings

rating_id
rating_date
rating_username
rating_topic
rating_value

What I want to be able to do is query this table to grab the highest rated topics and lowest rated topics in a given time period. My MySQL knowledge is pretty limited right now, so any help would be much appreciated.

Thanks.
 
These 2 queries will help:

This will select the MIN
select rating_topic,min(RatingValue)
(
select rating_topic,sum(rating_value)as RatingValue from RATING_TABLE
)


This will select the MAX
select rating_topic,max(RatingValue)
(
select rating_topic,sum(rating_value)as RatingValue from RATING_TABLE
)


Known is handfull, Unknown is worldfull
 
oops:
select rating_topic,min(RatingValue)
(
select rating_topic,sum(rating_value)as RatingValue from RATING_TABLE
)

should be:

select rating_topic,min(RatingValue) FROM
(
select rating_topic,sum(rating_value)as RatingValue from RATING_TABLE
)



i missed it in the max query too...

Known is handfull, Unknown is worldfull
 
vbkris, you also forgot the GROUP BYs :)

in fact, this is a lot trickier than it first appears, isn't it

Code:
select rating_topic
     , r as rating_value
  from ( 
       select rating_topic
            , sum(rating_value) as r
         from rating_table
       group
           by rating_topic
       ) as totals1
 where r = ( select min(r) 
  from ( 
       select rating_topic
            , sum(rating_value) as r
         from rating_table
       group
           by rating_topic
       ) as totals2  )
  or r = ( select max(r) 
  from ( 
       select rating_topic
            , sum(rating_value) as r
         from rating_table
       group
           by rating_topic
       ) as totals3  )
would look a lot cleaner if we could use a view, but you need mysql 5.1 for that

sp0om, did you ever get that other query working correctly outside of php?

:)

r937.com | rudy.ca
 
oops, when i was writing that i could not test it. i wrote the thing more conceptually...

Known is handfull, Unknown is worldfull
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top