vb.org Archive - Two-table query

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - Two-table query (https://vborg.vbsupport.ru/showthread.php?t=76607)

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:
[sql]
SELECT v.entryid, COUNT(v.userid) AS votecount, e.column4, e.column5... FROM votes v, entries e WHERE v.entryid = e.entryid AND e.month = 2 AND e.year = 2005 ORDER BY votecount DESC LIMIT 1
[/sql]
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.

Can you post the full table structure for both tables please :)

Code:

mysql> describe wdf_sotm_entry;

+-----------------+---------------------------------------+------+-----+---------+----------------+

| Field           | Type                                  | Null | Key | Default | Extra          |

+-----------------+---------------------------------------+------+-----+---------+----------------+

| entryid         | int(10) unsigned                      |      | PRI | NULL    | auto_increment |

| userid          | int(10) unsigned                      |      | MUL | 0       |                |

| month           | int(2) unsigned                       |      |     | 1       |                |

| year            | year(4)                               |      |     | 1970    |                |

| approvalstate   | enum('pending','approved','rejected') |      |     | pending |                |

| sitename        | varchar(255)                          |      |     |         |                |

| siteurl         | varchar(255)                          |      |     |         |                |

| sitedescription | mediumtext                            |      |     |         |                |

+-----------------+---------------------------------------+------+-----+---------+----------------+

8 rows in set (0.19 sec)



mysql> describe wdf_sotm_vote;

+---------+------------------+------+-----+---------+-------+

| Field   | Type             | Null | Key | Default | Extra |

+---------+------------------+------+-----+---------+-------+

| userid  | int(10) unsigned |      | PRI | 0       |       |

| entryid | int(10) unsigned |      |     | 0       |       |

+---------+------------------+------+-----+---------+-------+

2 rows in set (0.08 sec)

That would return the number of votes per entryid. I need to get the entryid that received the most votes for each month/year.

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.

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

X vBulletin 3.8.12 by vBS Debug Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)bbcode_code_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (6)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages:

Phrase Groups Available:

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.00951 seconds Memory Usage 1,724KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)bbcode_code_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (6)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: