MySQL GROUP BY multiple columns and fields

Question

I've tried to search answer here, there are few questions are quite similar with my question but i still can't figure out how to GROUP BY below.

MYSQL Structure:

ID | USERID | FRIENDID 
1  |   14   |    65
2  |   65   |    14
3  |   12   |    19
4  |   19   |    12

How to GROUP BY ID 1 & ID 2 and ID 3 & ID 4 with php query?

Result:

1. 14 & 65 
2. 12 & 19

You can't use GROUP BY to group by two different values. Do you just want to return the relationships (i.e. user 14 is related to 65)? — BenM
– BenM, Commented Jan 12, 2013 at 13:14
Hi Ben, i want to display only 1 result, either 14&65 or 65&14. — richard
– richard, Commented Jan 12, 2013 at 13:17
I understand that - but my question really is how you determine that only those two should be displayed. Is it when one USERID is already existing in FRIENDID? — BenM
– BenM, Commented Jan 12, 2013 at 13:18
And you want to return the USERID and FRIENDID fields, rather than the ID? — BenM
– BenM, Commented Jan 12, 2013 at 13:21

John Woo · Accepted Answer · 2013-01-12 13:24:37Z

8

SELECT LEAST(USERID, FRIENDID) as x, 
       GREATEST(USERID, FRIENDID) as y
FROM   TableName
GROUP  BY x, y

SQLFIddle Demo

answered Jan 12, 2013 at 13:24

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

Comments

Mathew · Accepted Answer · 2013-01-12 13:22:18Z

-2

SELECT CONCAT(USERID, ' & ', FRIENDID) FROM table

answered Jan 12, 2013 at 13:22

Mathew

8,2997 gold badges40 silver badges59 bronze badges

7 Comments

BenM Over a year ago

It returns 4 rows, when the question asked for only distinct pairs to be returned.

Mathew Over a year ago

My solution does what you ask - i.e. return the results grouped like this "id1 & id2". Perhaps you need to improve the wording of your question

Mathew Over a year ago

@BenM nowhere in the question is the word "distinct" mentioned

BenM Over a year ago

GROUP BY ID 1 & ID 2 **and** ID 3 & ID 4 <-- that's a direct quote. Heck, he even indicated his desired output...

Mathew Over a year ago

The wording of this question is still appalling, it even asks for a php query. Any wonder I assumed the desired output was a loose example of the row output, rather than the specific result set...

|

Collectives™ on Stack Overflow

MySQL GROUP BY multiple columns and fields

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related