Go Back   vb.org Archive > vBulletin 3 Discussion > vB3 Programming Discussions
FAQ Community Calendar Today's Posts Search

Reply
 
Thread Tools Display Modes
  #1  
Old 02-19-2005, 12:44 AM
filburt1 filburt1 is offline
 
Join Date: Feb 2002
Location: Maryland, US
Posts: 6,144
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default Two-table query

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.
Reply With Quote
  #2  
Old 02-19-2005, 11:06 AM
Dean C's Avatar
Dean C Dean C is offline
 
Join Date: Jan 2002
Location: England
Posts: 9,071
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Can you post the full table structure for both tables please
Reply With Quote
  #3  
Old 02-19-2005, 03:02 PM
filburt1 filburt1 is offline
 
Join Date: Feb 2002
Location: Maryland, US
Posts: 6,144
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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)
Reply With Quote
  #4  
Old 02-19-2005, 04:37 PM
filburt1 filburt1 is offline
 
Join Date: Feb 2002
Location: Maryland, US
Posts: 6,144
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

That would return the number of votes per entryid. I need to get the entryid that received the most votes for each month/year.
Reply With Quote
  #5  
Old 02-19-2005, 04:39 PM
Dean C's Avatar
Dean C Dean C is offline
 
Join Date: Jan 2002
Location: England
Posts: 9,071
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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.
Reply With Quote
  #6  
Old 02-19-2005, 04:42 PM
filburt1 filburt1 is offline
 
Join Date: Feb 2002
Location: Maryland, US
Posts: 6,144
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 02:32 AM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.03654 seconds
  • Memory Usage 2,209KB
  • Queries Executed 13 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)bbcode_code
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (6)post_thanks_box
  • (6)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (6)post_thanks_postbit_info
  • (6)postbit
  • (6)postbit_onlinestatus
  • (6)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.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/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete