CODEWITHSUNDEEP
mysqlgroup-by
timenz
timenz

Reputation: 37

MySQL Query group by to get the last id

this is my table 

id | sender | receiver | msg  
-----------------------------
1  | jon    | jack     | buzz ...  
2  | jack   | jon      | ?  
3  | adel   | jon      | met me soon  
4  | jon    | adel     | okay  
5  | alex   | jon      | where ar u ?  
6  | jon    | adel     | okay  
7  | adel   | alex     | don't forget the party  
8  | jon    | jack     | may i borrow ur car 
9  | alex   | adel     | of course
10 | jack   | jon      | ok
11 | jack   | jon      | watch the gas
12 | alex   | jon      | i'm dying here
13 | jon    | alex     | 5 mnt ..

and i want get list last messages of jon with his friend like this

id | sender | receiver | msg
-------------------------------------------
13 | jon    | alex     | 5 mnt ..
11 | jack   | jon      | watch the gas
6  | jon    | adel     | okay

How to query, to get that result ?

Upvotes: 2

Views: 8200

Answers (6)

Swapnil Kumbhar
Swapnil Kumbhar

Reputation: 460

This is simple query you can change according to your logic

SELECT * FROM tbl_enquiry WHERE id IN (
  SELECT Max(id) FROM tbl_enquiry WHERE date = '06/25/2015' GROUP BY cust_id 
) LIMIT 0 , 30

Upvotes: -1

Sashi Kant
Sashi Kant

Reputation: 13465

Try this::

SELECT * 
FROM table 
WHERE sender ='jon' OR receiver='jon' 
GROUP BY msg 
ORDER BY id desc

Upvotes: 1

Olivier Coilland
Olivier Coilland

Reputation: 3096

Use this:

SELECT *
FROM table
WHERE id IN (
    SELECT MAX(id)
    FROM table
    WHERE sender = 'jon' 
       OR receiver = 'jon'
    GROUP BY IF(sender = "jon", receiver, sender)
) 
ORDER BY id DESC;

Edit: Thanks for the edit ypercube, I really forgot something important here :)

Upvotes: 7

Zagor23
Zagor23

Reputation: 1963

This returns exactly what you want:

SELECT * FROM 
(SELECT * FROM `table` WHERE sender="jon" OR receiver="jon" ORDER BY id DESC) a 
GROUP BY IF(sender = "jon", receiver, sender) 
ORDER BY id DESC

Sorry for wrong answer first time, I guess I didn't read the question carefully. :(

Here's the test:

http://sqlfiddle.com/#!2/e66ac/2

Upvotes: 3

sel
sel

Reputation: 4957

SELECT * FROM 
(SELECT * FROM tblA WHERE sender="jon" OR receiver="jon" ORDER BY id DESC) a 
GROUP BY IF(sender = "jon", receiver, sender)

Please refer to http://sqlfiddle.com/#!2/44099/1

Upvotes: 2

haynar
haynar

Reputation: 6030

I think you need something like this:

SELECT * FROM my_table WHERE sender="jon" OR receiver="jon" ORDER BY id DESC

Upvotes: 0

Related Questions