Get unique rows with combination of two columns

1
votes

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

2
mysqlsql
"then it should show only one row." - which one - and why? – Paul Spiegel
@PaulSpiegel I think the OP just wants the latest message exchanged between each pair of users. – Tim Biegeleisen
@TimBiegeleisen I guess that too. But it would be better, if OP clears that in the question. – Paul Spiegel
It will be of latest time from those two @PaulSpiegel – Code Spy

2 Answers

3
votes

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

0
votes

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