Input data:

I want the post_id that received the most likes per one person.

I need to pick only one row with satisfying several conditions: Max(like_count), per 1 id (Can be duplicated), npost_id (primary key)

Here's what I've tried:

SELECT npost_id, mid, like_count 
FROM feed
WHERE (mid, like_count) IN (SELECT mid, MAX(like_count) 
                            FROM feed
                            GROUP BY mid)

I can't think of anything other than that query.

CodePudding user response：

In MySQL 8.0, one option to retrieve only one row for each combination of <mid, like_count> is to use the ROW_NUMBER window function, which allows you to assign a ranking value for each combination of <mid, like_count> (a partition). In order to get only one element for each of these, it's sufficient to filter out rows that have ranking bigger than 1 (the rows that have repeated <mid, like_count> values).

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY mid, like_count ORDER BY npost_id) AS rn
    FROM tab
)
SELECT npost_id, mid, like_count
FROM cte
WHERE rn = 1

Check the demo here.

In MySQL 5.7, you can instead aggregate on the two different combination of <mid, like_count> and take the smaller value for the npost_id field (given that you are willing to accept any npost_id value for the partition).

SELECT MIN(npost_id) AS npost_id, 
       mid, 
       like_count
FROM tab
GROUP BY mid, like_count

Check the demo here.