This is a discussion on Weighing popularity within the MySQL Database forums, part of the Database Forums category; I'm a little stuck on this. I have a table like this: posts ( id integer, item_id integer, linked_on datetime ) ...
|
|||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
|
|||
|
I'm a little stuck on this. I have a table like this:
posts ( id integer, item_id integer, linked_on datetime ) where item_id is a reference to another table and many items will show up multiple times over an amount of time. I'd like to display the posts table based on the weight of how many times an item shows up over x number of hours. Data might look something like this (assuming it's 2007-04-15 11:30): 1, 3, 2007-04-14 23:20 2, 1, 2007-04-15 07:00 3, 1, 2007-04-15 08:30 4, 2, 2007-04-15 11:00 5, 1, 2007-04-15 11:10 6, 2, 2007-04-15 11:12 so, in this case item #2 would bubble up to the top if show popularity over the last hour (followed by item #1 then item #3), but item #1 would bubble to the top if looking at popularity over the last 6 hours (followed by item #2 then item #3). Thanks for the help! |