MYSQL GROUP BY multiple different values with specific column

Question

MYSQL Structure:

 ID | USERID | FRIENDID | Type
-------------------------------
 1  |   10   |    20    | Gold
 2  |   20   |    10    | Gold

 3  |   30   |    40    | Silver
 4  |   40   |    30    | Silver

 5  |   50   |    60    | Gold
 6  |   60   |    50    | Gold

 7  |   70   |    80    | Bronze
 8  |   80   |    70    | Bronze

 9  |   90   |   100    | Bronze
10  |  100   |    90    | Bronze

What i want is GROUP (ID 1 & ID 2) and (ID 5 & ID 6) because they are "gold" type, NOT GROUP BY TYPE.

Return Results:

 1. 10 & 20, type:gold. (GROUP)

 3. 30 & 40, type:silver.
 4. 40 & 30, type:silver.

 5. 50 & 60, type:gold. (GROUP)

 7. 70 & 80, type:bronze.
 8. 80 & 70, type:bronze.

 9. 90 & 100, type:bronze.
10. 100 & 90, type:bronze.

How to do that with php query?

Demo: http://sqlfiddle.com/#!2/13bd3/1

are the ids you want to group static numbers known before hand? — Sebas
– Sebas, Commented Jan 12, 2013 at 18:28

Gordon Linoff · Accepted Answer · 2013-01-12 18:45:30Z

7

What you need to do is to add an additional grouping clause based on the type. When it is 'gold', you get a constant. Otherwise, you use the id:

select least(userid, friendid), greatest(userid, friendid), type
from t
group by least(userid, friendid), greatest(userid, friendid),
         (case when type = 'gold' then 0 else id end)

This does rearrange the order of the ids for non-gold types. If ordering is important, the SQL is a little more complicated:

select (case when type = 'gold' then least(userid, friendid) else userid end),
       (case when type = 'gold' then greatest(userid, friendid) else friendid end),
       type
from t
group by least(userid, friendid), greatest(userid, friendid),
         (case when type = 'gold' then 0 else id end)

answered Jan 12, 2013 at 18:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

richard Over a year ago

Perfect! Thanks Gordon! :)

Muhammad Raheel · Accepted Answer · 2013-01-12 18:30:16Z

3

SELECT GROUP_CONCAT(friend_id) , type FROM mytable GROUP BY type

Demo

answered Jan 12, 2013 at 18:30

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

1 Comment

Zhang Buzz Over a year ago

Although this may not be the correct answer for this specific situation, but the group_concat saved my time.

Saharsh Shah · Accepted Answer · 2013-01-16 05:23:25Z

0

Try this:

SELECT id, userid, friendid, type 
FROM (SELECT id, userid, friendid, type, 
             IF(LOWER(type) = 'gold', IF(@lasttype=(@lasttype:=TYPE), @auto, @auto:=@auto+1), @auto:=@auto+1)  indx 
      FROM tablename, (SELECT @auto:=1, @lasttype:=0) a 
      ORDER BY id) a 
GROUP BY indx;

edited Jan 16, 2013 at 5:23

answered Jan 12, 2013 at 18:49

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Collectives™ on Stack Overflow

MYSQL GROUP BY multiple different values with specific column

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related