I want to select a list of non-duplicate records, that invlove a certain user (either in
user_from). I want to retrieve the other user and also the latest content in that record. The list must not have duplications in the other user selected.
For example, I have the following set of records
id user_to user_from content time 1 1 2 ABC 2013-11-05 2 4 2 BBC 2013-11-06 3 3 1 CBC 2013-11-07 4 5 1 ABC 2013-11-08 5 1 2 AAC 2013-11-09 6 5 1 ABB 2013-11-10 7 3 4 CBC 2013-11-11 8 1 2 ACC 2013-11-12
In this case, If the parameter to provide is 1, I want to select record
3,6,8 , the others are not selected because either they are duplicated and older or they do not involve
1. I have looked into this post and tried something like this:
SELECT u, content, date FROM( (SELECT user_from AS u, MAX(time) AS date, content FROM t1 WHERE user_to = :user ) UNION (SELECT user_to AS u, MAX(time) AS date, content FROM t1 WHERE user_from = :user ) ) t2 WHERE date IN (SELECT MAX(date) FROM t2 GROUP BY u)
But no, can't get it done.
Any idea how to write the query? Thanks!