The Arcive of Official vBulletin Modifications Site.It is not a VB3 engine, just a parsed copy! |
|
#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
Can you post the full table structure for both tables please
|
#3
|
|||
|
|||
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) |
#4
|
|||
|
|||
That would return the number of votes per entryid. I need to get the entryid that received the most votes for each month/year.
|
#5
|
||||
|
||||
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.
|
#6
|
|||
|
|||
Not for multiple months/years (only for the current month/year), but it should be fairly easy to add your own.
|
Thread Tools | |
Display Modes | |
|
|
X vBulletin 3.8.12 by vBS Debug Information | |
---|---|
|
|
More Information | |
Template Usage:
Phrase Groups Available:
|
Included Files:
Hooks Called:
|