CODEWITHSUNDEEP
phpmysql
Scott Helme
Scott Helme

Reputation: 4799

MySQL query to select based on item with highest votes

I have a simple web app and when a user clicks favorite on an image the database stores a user_id and the image_id they were viewing, the table looks like this:

Favorites
---------------------
-user_id - image_id -
---------------------
-abc     - 123      -
-abc     - 456      -
-def     - 123      -
---------------------

I'm trying to find the top 10 favorite images (globally), that is the 10 images with the most favorites overall. The query simply needs to find the 10 image_id values that occur most frequently. So far I've tried a few things along the lines of

SELECT image_id, COUNT(*) FROM favourites GROUP BY image_id LIMIT 100 ORDER DESC

What would be the right query to accomplish this?

Upvotes: 1

Views: 840

Answers (2)

trrrrrrm
trrrrrrm

Reputation: 11802

Try this:

SELECT
  image_id, count(image_id)
FROM Favorites
GROUP BY image_id
ORDER BY 2 DESC
LIMIT 10

Upvotes: 0

Fluffeh
Fluffeh

Reputation: 33502

The following query should do the trick, it is almost the same as your code, but the last bit is different:

select
    image_id,
    count(*)
from
    favourites
group by
    image_id
order by
    count(*) desc
limit 10

You might also want to have a read of Q&A that I wrote which covers off a lot of stuff like this in a great deal of depth.

Edit:

To answer one of the comments below, does using count(*) in the order by statement cause it to calculate again?

No.

mysql> select * from test2;
+------+-------+-------+
| id   | barry | third |
+------+-------+-------+
|    1 | ccc   |  NULL |
| NULL | d     |     1 |
| NULL | d     |     2 |
| NULL | d     |     3 |
+------+-------+-------+
4 rows in set (0.00 sec)

mysql> explain select barry, max(third) from test2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test2 | ALL  | NULL          | NULL | NULL    | NULL |    4 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.11 sec)

mysql> explain select barry, max(third) from test2 order by barry;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test2 | ALL  | NULL          | NULL | NULL    | NULL |    4 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)


mysql> explain select barry, max(third) from test2 order by max(third);
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
|  1 | SIMPLE      | test2 | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
1 row in set (0.00 sec)

You can see from this that it stores the data in temporary and uses it from there.

Upvotes: 6

Related Questions