CODEWITHSUNDEEP
mysqlgroup-by
Fela Maslen
Fela Maslen

Reputation: 2162

Group by grouped column?

I have a table of node likes, which looks roughly like this:

lid nid uid type
1   23  3   like
2   23  1   like
3   49  3   dislike
4   11  6   like

lid = unique ID for this table, nid = "node" (content) ID, uid = user ID and type is self explanatory.

With this query:

SELECT nid, COUNT(lid) AS score, type
FROM node_likes
INNER JOIN users ON node_likes.uid = users.uid
GROUP BY nid, type

I can get each node with its like and dislike scores. The inner join is irrelevant; some (dis)likes are from users that no longer exist, and the join is to eliminate them.

The result looks like this:

nid score type
307 4     like
307 1     dislike
404 24    like

How can I then sub-group this query by type, and return the top-scoring node ID for each "like" type (like/dislike)?

Ie.

nid score type
404 24    like
307 1     dislike

Upvotes: 0

Views: 5816

Answers (3)

Vatev
Vatev

Reputation: 7590

SELECT
    SUBSTRING_INDEX(GROUP_CONCAT(nid ORDER BY likes DESC),',',1) as most_likes_nid,
    MAX(likes) as most_likes,
    SUBSTRING_INDEX(GROUP_CONCAT(nid ORDER BY dislikes DESC),',',1) as most_dislikes_nid,
    MAX(dislikes) as most_dislikes
FROM (
    SELECT 
        nid,
        COUNT(IF(type = 'like', 1, null)) as likes,
        COUNT(IF(type = 'dislike', 1 ,null)) as dislikes
    FROM node_likes
    GROUP BY nid 
) as t

Upvotes: 1

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

Try this: 

SELECT 
    nid, max(score) as score, type
FROM (
    SELECT nid, COUNT(lid) AS score, type
    FROM node_likes
    INNER JOIN users ON node_likes.uid = users.uid
    GROUP BY nid, type
) results
GROUP BY type
ORDER BY type DESC, score DESC

Upvotes: 0

ferensick
ferensick

Reputation: 21

    SELECT nid, COUNT(lid) AS score, type
    FROM node_likes
    INNER JOIN users ON node_likes.uid = users.uid
    GROUP BY nid, type        
    ORDER BY type DESC, score DESC;

may do the trick.

Upvotes: 0

Related Questions