[SOLVED] Most common value

[MattR]

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??

[MattR]

Nobody has any ideas??

[PFMaBiSmAd]

You need to remove the single-quotes from around the alias name where it is used in the ORDER BY term.

[MattR]

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.

[PFMaBiSmAd]

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)

[MattR]

Brilliant!! Thanks again