CODEWITHSUNDEEP
mysql
murtuzakothawala
murtuzakothawala

Reputation: 182

Grouping two columns in mysql

I have a query? I have a table as

    sender_user_id  receiver_user_id        
        2               3           
        3               2           
        2               7       
        2               8           
        7               3           
       10               6               
        2               3

and i want to group the columns by sender_user_id and receiver_user_id also (sender id=2 and receiver_id=3) and (sender id=3 and receiver_id=2) should be grouped as one column with max timestamp

Upvotes: 2

Views: 122

Answers (6)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

Try the GROUP BY as a subquery and do the CONCAT afterwards

SELECT
    CONCAT(sender_user_id,' ',receiver_user_id) sender_receiver,
    max_datestamp
FROM
(
    SELECT sender_user_id,receiver_user_id, max(datestamp) max_datestamp
    FROM temptable GROUP BY sender_user_id,receiver_user_id
) A;

Make sure temptable is properly indexed

ALTER TABLE temptable ADD INDEX sender_receiver_ndx (sender_user_id,receiver_user_id);

Upvotes: 1

neuDev33
neuDev33

Reputation: 1623

Not clear what you mean. Do you want something like 

   group by sender_id, reciever_id
   order by timestamp desc

Upvotes: 1

Justin Pihony
Justin Pihony

Reputation: 67075

Is this what you are looking for?

SELECT CONCAT(sender_user_id,' ', receiver_user_id ) as newcolumn, max(datestamp)
from temptable
group by CONCAT(sender_user_id,' ', receiver_user_id ) ;

Really crude, quick, SQLFiddle Solution

Upvotes: 2

Guillaume U
Guillaume U

Reputation: 436

try

SELECT *, MAX(timestamp) FROM <my_table> GROUP BY sender_user_id, receiver_user_id

Upvotes: 1

liquorvicar
liquorvicar

Reputation: 6106

Not sure if you can do this with a GROUP BY combining the two columns. You could try a UNION

SELECT user1,user2,MAX(timestamp) FROM 
(SELECT
  sender_user_id AS user1,
  receiver_user_id AS user2,
  timestamp
FROM table
) AS table1
UNION DISTINCT
(SELECT 
  receiver_user_id AS user1,
  sender_user_id AS user2,
  timestamp
FROM table) AS table2
GROUP BY user1,user2

Upvotes: 1

Sergio
Sergio

Reputation: 8259

Try this: 

select max(timestamp) from yourTable group by sender_user_id, receiver_user_id

Upvotes: 1

Related Questions