Most liked photo

Question

Im trying to find the most liked photo in my DB. There are PHOTOS,LIKES,USERS tables. I tryed to find most liked photo and user who posted the picture with only LIKES and USERS and got an error.

SELECT 
    users.username,
    photo_id,
    COUNT(*) AS most_liked
FROM
    likes
    INNER JOIN users ON users.id=likes.user_id
GROUP BY
    photo_id
ORDER BY
    most_liked DESC
LIMIT
    1;

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ig_clone.users.username' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Can I do this with only 2 tables or should I use 3rd table as well?

What part of the error message do you not understand? It seems very clear and suggests a way to fix the problem. — Gordon Linoff, Nov 08 '18 at 20:08
You need to add the user name to your group by clause Group by phto_id, users.username — Brad, Nov 08 '18 at 20:08
Getting username is indeterministic here. It can be any username who has liked that photo. That is why MySQL is throwing error. — Madhur Bhaiya, Nov 08 '18 at 20:08
@MadhurBhaiya Thanks. I thought that I was selecting a user who posted the photo, but user_id are the users who liked the photo — Hayk, Nov 09 '18 at 16:18
Does this answer your question? [Error related to only\_full\_group\_by when executing a query in MySql](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) — miken32, Feb 28 '23 at 16:46

score 1 · Accepted Answer · answered Nov 08 '18 at 20:11

You need to use a subquery to to find most-liked-photos (using only the likes table) separately from actual photo information (from the photos) table - which is then joined to users. I assume your photos table has a user_id column.

SELECT
    users.username,
    photos.photo_id,
    photo_likes.like_count

FROM
    photos
    INNER JOIN
    (
        SELECT
            photo_id,
            COUNT(*) AS like_count
        FROM
            likes
        GROUP BY
            photo_id
    ) AS photo_likes
    INNER JOIN users ON users.id = photos.user_id

ORDER BY
    like_count DESC

LIMIT
    1

Most liked photo

1 Answers1