PDA

View Full Version : Two-table query


filburt1
19 Feb 2005, 01:44
I have a table called (for example) "votes" with the following structure:

"userid:" INT(10) UNSIGNED NOT NULL DEFAULT 0
"entryid:" INT(10) UNSIGNED NOT NULL DEFAULT 0

...with a unique grouping on those two columns.

It is related to another table I have called "entries" with the following general structure:

"entryid:" same as "votes"'s entryid column (the two tables relate to each other through this column)
"year": YEAR DEFAULT 1970
"month": INT(2) UNSIGNED DEFAULT 1
column4
column5
.
.
.

...plus some additional columns in "entries."

For each unique year/month combination, how can I find out which entry got the most number of votes?

For example, I believe this query gets the entryid and the associated entry data that has the most votes in the "votes" table for February 2005:


Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------


Without querying in a loop, how can I extend the query so that it gets the winning entry for each month and year (i.e., the winner for February 2005, March 2005, etc.) instead of just one given month? I do not know how to do this given MySQL does not let you give ORDER BY power over GROUP BY.

Dean C
19 Feb 2005, 12:06
Can you post the full table structure for both tables please :)

filburt1
19 Feb 2005, 16:02
Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------

filburt1
19 Feb 2005, 17:37
That would return the number of votes per entryid. I need to get the entryid that received the most votes for each month/year.

Dean C
19 Feb 2005, 17:39
Sorry, deleted my post before you got round to seeing it :) You got some test data? I'll have a play around in an hours time if you attach a zipped up sql file.

filburt1
19 Feb 2005, 17:42
Not for multiple months/years (only for the current month/year), but it should be fairly easy to add your own. :)