1

Having this private chat table enter image description here

On my message page I want to show latest conversations list. List will be like if am sending message from pc to pc2 and pc2 sending back to pc then it should show only one row.

I tried this query

SELECT id, col2, col3, col4 FROM yourtable GROUP BY col2, col3;

Selecting distinct 2 columns combination in mysql

But it's resulting in

enter image description here

Update:

I tried this query

SELECT * FROM tbl_primessages Where frmid = 3466 OR toid = 3466 GROUP BY frmid, toid ORDER BY tbl_primessages.timestemp DESC

Update 2 It should be with the latest time from two

Community
  • 1
  • 1
Code Spy
  • 9,626
  • 4
  • 66
  • 46

2 Answers2

3

The query below used a least/greatest trick to group together messages involving the same pair of users. Then, we can retain the latest conversation per pair of people.

SELECT t1.*
FROM tbl_primessages t1
INNER JOIN
(
    SELECT
        LEAST(frmid, toid) AS frmid,
        GREATEST(frmid, toid) AS toid,
        MAX(timestamp) AS latest_ts
    FROM tbl_primessages
    GROUP BY LEAST(frmid, toid), GREATEST(frmid, toid)
) t2
    ON LEAST(t1.frmid, t1.toid)    = t2.frmid AND
       GREATEST(t1.frmid, t1.toid) = t2.toid  AND
       t1.timestamp = t2.latest_ts

Demo here:

Rextester

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

In the SELECT use only fields from the GROUP BY plus some aggregate functions (COUNT, AVG, MIN, MAX, etc).

SELECT frmid, toid
FROM tbl_primessages
Where frmid = 3466 OR toid = 3466
GROUP BY frmid, toid
Edward
  • 334
  • 3
  • 5