im trying to find the most liked photo in db, there are USERS, PHOTOS, LIKES, COMMENTS, but in the l-CodePudding

CREATE TABLE likes(
    user_id INT NOT NULL,
    photo_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(user_id) REFERENCES users(id),
    FOREIGN KEY(photo_id) REFERENCES photos(id),
    PRIMARY KEY(user_id,photo_id)
);

Im not able to think of any solution

CodePudding user response：

You can use the below query to find the most liked picture

SELECT PHOTO_ID, COUNT(*) AS NO_OF_LIKES
FROM LIKES
GROUP BY PHOTO_ID
ORDER BY NO_OF_LIKES DESC
LIMI 1

You can use the having clause like below

SELECT PHOTO_ID, COUNT(*) AS NO_OF_LIKES
FROM LIKES
GROUP BY PHOTO_ID
HAVING COUNT(*) = (
       SELECT MAX(COUNT(*))
       FROM LIKES
       GROUP BY PHOTO_ID
)