MYSQL GROUP BY multiple different values with specific column
22,030
Solution 1
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)
Solution 2
SELECT GROUP_CONCAT(friend_id) , type FROM mytable GROUP BY type
Author by
richard
Updated on January 16, 2020Comments
-
richard over 4 years
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?
-
richard over 11 yearsPerfect! Thanks Gordon! :)
-
Zhang Buzz almost 7 yearsAlthough this may not be the correct answer for this specific situation, but the group_concat saved my time.