MattR Posted November 9, 2009 Share Posted November 9, 2009 Read the rules, this is what's applicable. I have a table that records an ID and a time, and some other stuff that isn't relevant here: +---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | rid | int(11) | NO | PRI | NULL | auto_increment | | qid | int(11) | YES | | NULL | | | location | varchar(255) | NO | | NULL | | | extralocation | text | NO | | NULL | | | time | int(10) | NO | | NULL | | +---------------+--------------+------+-----+---------+----------------+ Records: +------+------------+ | qid | time | +------+------------+ | 299 | 1256679944 | | 147 | 1256679948 | | 69 | 1256679949 | | 218 | 1256679969 | | 3 | 1256679975 | | 10 | 1256680053 | | 140 | 1256680068 | | 249 | 1256680096 | | 236 | 1256680103 | | 64 | 1256680119 | +------+------------+ ... etc etc. Times are old but it's just an example. Query that I found on the internet: SELECT `qid`, COUNT(*) AS 'Last24Hours' FROM `lost_records` WHERE `time` BETWEEN '1257721753' AND '1257808153' GROUP BY `qid` ORDER BY 'Last24Hours' DESC LIMIT 1; MySQL 5.1.40 Basically I want to find the most common qid over the last day. It physically works, there's no errors, but it's not always accurate. For example, it returned qid 1 and the count was 1, but there were some qids where 3+ times. Anyone see what's wrong with it?? Quote Link to comment https://forums.phpfreaks.com/topic/180913-solved-most-common-value/ Share on other sites More sharing options...
MattR Posted November 11, 2009 Author Share Posted November 11, 2009 Nobody has any ideas?? Quote Link to comment https://forums.phpfreaks.com/topic/180913-solved-most-common-value/#findComment-955475 Share on other sites More sharing options...
PFMaBiSmAd Posted November 11, 2009 Share Posted November 11, 2009 You need to remove the single-quotes from around the alias name where it is used in the ORDER BY term. Quote Link to comment https://forums.phpfreaks.com/topic/180913-solved-most-common-value/#findComment-955489 Share on other sites More sharing options...
MattR Posted November 13, 2009 Author Share Posted November 13, 2009 Ah, so simple!! Thank you. One more question, if it returns more than one value, how would I go about getting them all?? For example, if COUNT(*) is 28 but there's more than 1 qid with a count of 28, how could I get all of the qids with a count of 28?? If I remove the LIMIT 1 it returns everything. Quote Link to comment https://forums.phpfreaks.com/topic/180913-solved-most-common-value/#findComment-957114 Share on other sites More sharing options...
PFMaBiSmAd Posted November 13, 2009 Share Posted November 13, 2009 SELECT `qid`, COUNT(*) AS 'Last24Hours' FROM `lost_records` WHERE `time` BETWEEN '1257721753' AND '1257808153' GROUP BY `qid` HAVING Last24Hours = (SELECT COUNT(*) FROM `lost_records` WHERE `time` BETWEEN '1257721753' AND '1257808153' GROUP BY `qid` ORDER BY COUNT(*) DESC LIMIT 1) Quote Link to comment https://forums.phpfreaks.com/topic/180913-solved-most-common-value/#findComment-957158 Share on other sites More sharing options...
MattR Posted November 14, 2009 Author Share Posted November 14, 2009 Brilliant!! Thanks again Quote Link to comment https://forums.phpfreaks.com/topic/180913-solved-most-common-value/#findComment-957649 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.