Vikram Sangat Vikram Sangat - 3 months ago 14
MySQL Question

Select Uniq rows from two columns according to given user in MySql

I am creating a chat system where my database is looks like

from to msg date
---- -- ---- -----------------
a b hi 12-3-15 12:04:21
b a hi 12-3-15 12:12:21
c b hi 12-3-15 12:14:21
d b hi 12-3-15 12:14:21
a b msg1 12-3-15 12:12:21
a b msg2 12-3-15 12:50:23
a b msg3 12-3-15 13:44:21
b a msg1 12-3-15 15:12:21
b e hi 12-3-15 15:32:21
b c hi 12-3-15 16:12:21


I am trying make a query that returns last message sent by use 'b' or last message received by use 'b' and output like

Actor b
----
c
e
a
d


I have written a query

(SELECT `to`
, `from`
FROM databaseTable
WHERE from = 'b'
ORDER BY date desc)

UNION

(SELECT `to`
, `from`
FROM databaseTable
WHERE to = 'b'
ORDER BY date desc )


but it is not returning what I want

Answer

Try the following:

select distinct `to` as ActorB
from
(
    select * from
    (
        SELECT `to`,`date` 
        FROM databaseTable 
        WHERE from = 'b'
        UNION 
        SELECT  `from` , `date`
        FROM databaseTable 
        WHERE to = 'b'
    ) temp
    ORDER BY date desc
) temp

Above query should work.

Comments