Re: ranking product based on votes in a given week

how about a WHERE clause. Maybe something like: $query = "SELECT WEEK(date) as week, count(*) as hits, listingID as lidcount FROM invotestotal WHERE WEEK(date) = 8 ORDER BY hits DESC"; Ryan Schefke wrote:

I am trying to echo the ranking of votes (highest to lowest) that a product received in a given week. See example of desired output. In the query below, It's working great and outputs ID, votes, and week; however, I can't figure out how to get the ranking for a given week. In the example below, how would I be able to output values in the Ranking column for week 8 so that ID 1 is ranked 1, ID 3 is ranked 2, etc. Help? Example ranking:

ID         Votes    Ranking            Week

1          29         1                      8

3          23         2                      8

7          2          3                      8

1          2          1                      10

-------------------- QUERY ------------------------------ $query = "SELECT WEEK(date) as week, count(*) as hits, listingID as lidcount FROM invotestotal GROUP BY week, lidcount ORDER BY week DESC, hits DESC"; $result = @mysql_query($query); while ($row = mysql_fetch_array($result, MYSQL_NUM)) { $printweek = $row[0]; $countinvotes = $row[1]; $lidnumber = $row[2]; Then, echo values in table rows... } -------------------- QUERY ------------------------------ -------------------- DEFINITIONS IN QUERY ------------------------------ WEEK(date) as week :: week vote took place count(*) as hits :: counts total votes listingID as lidcount :: ID of the product that received the vote -------------------- DEFINITIONS IN QUERY ------------------------------ Best regards, Ryan

-- Respectfully, Ligaya Turmelle "Life is a game.... so have fun"