Selecting latest row of record with distinct parameter

Question

I want to select a list of non-duplicate records, that invlove a certain user (either in user_to or 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!

Why not the 4 registry instead the 3? because you say that either are duplicated or older, but the older is 3 not 4! — Jorge Campos, Nov 27 '13 at 12:31
I mean (they are duplicated AND older) OR (they do not involve 1). Record 4 involves the other user 5 (as user_to), which has a newer record in 6. Record 3 involves user 3 which does not have a newer record. Sorry for the confusion. — user2335065, Nov 27 '13 at 12:37
Even with your edit the data that you want does not fit the showed sample example. If you want everithing that is user 1 (from and to) and that is not duplicate and is max date the result should be: 3, 4, 6 and 8. Please, look at your data. — Jorge Campos, Nov 27 '13 at 12:55
better 3, 4, 5, 6 and 8. See it here: http://sqlfiddle.com/#!2/198d1e/8 — Jorge Campos, Nov 27 '13 at 13:03
Record 4 should not be selected as there is a newer record 6 that also involves user 5. Record 5 should not be selected as there is a newer record 8 that also involves user 2. I want to select the user of the counter-part (so if 1 is user_to, I want user_from, and if 1 is user_from, I want user_to), therefore, you can see in my original sql, I have union of two queries with the same alias u — user2335065, Nov 27 '13 at 14:00
ok. but as I only want the user_id of the other user, is there a way to select only user_to when user_from=1 and user_from when user_to=1 ? — user2335065, Nov 27 '13 at 14:29

Jorge Campos · Answer 1 · 2013-11-27T14:36:39.147

0

Your query should be this:

 select m.* from 
 message m,
 ( select user_to,
          user_from, 
          max(dtime) mxdate 
     from message 
    where user_from = 1 or user_to = 1
    group by user_to, user_from) m2
 where m.dtime = m2.mxdate
   and (m.user_from = 1 or m.user_to = 1)

See it here at fiddle: http://sqlfiddle.com/#!2/13d4e/4

As you ask in comments: ok. but as I only want the user_id of the other user, is there a way to select only user_to when user_from=1 and user_from when user_to=1 ?

select if(m.user_to=1,m.user_from,m.user_to) as user, 
       m.content, 
       m.dtime
  from 
    message m,
    ( select user_to,
             user_from, 
             max(dtime) mxdate 
        from message 
       where user_from = 1 or user_to = 1
       group by user_to, user_from) m2
 where m.dtime = m2.mxdate
   and (m.user_from = 1 or m.user_to = 1)

see it here: http://sqlfiddle.com/#!2/13d4e/5

edited Nov 27 '13 at 14:36

answered Nov 27 '13 at 12:42

Jorge Campos

22,647
7
56
87

Oh.. my example isn't good enough... I mean user 1 can be either in user_from or user_to. See my edited example. – user2335065 Nov 27 '13 at 12:47
This isn't right. 'group by user_to, user_from' won't satisfy the requirement for unique conversations. – Strawberry Nov 27 '13 at 15:10
@Strawberry I think, as me at first, you missundestood the OPs requirements. If you look on the comments you will see that this is exactly what he want. – Jorge Campos Nov 27 '13 at 15:30
Well in that case I can't pretend I really understand the requirement anymore :-(. You may be spot on but, either way, your solution still assumes that dtime is unique (well for user 1 anyway). I agree that this is highly likely, but I'm not sure it should be relied upon. – Strawberry Nov 27 '13 at 15:36
I understand what you are saying, that in one of my max(dates) there is a date like '2013-11-11' and some other user (from and to) as this date it will match my join, but as you can see I'm using the same filter on the outside query as inside `and (m.user_from = 1 or m.user_to = 1)` – Jorge Campos Nov 27 '13 at 15:57

Ismail Gunes · Answer 2 · 2013-11-27T16:17:41.737

0

if filtering is "1" add this to the query of @Jorge Campos

where user_from = 1 OR user_to=1

edited Nov 27 '13 at 16:17

answered Nov 27 '13 at 13:26

Ismail Gunes

548
1
9
24

by that I can only select user_from. Even if I also select the field user_to, I cannot determine which (user_to/user_from ?) is the value I want, as I don't want the user=1 value, but only the other one – user2335065 Nov 27 '13 at 14:03
With the rectified answer @Jorge Campos the query chooses either user_from or user_to if it's set to "1". Or we don't understand what you want ? – Ismail Gunes Nov 27 '13 at 16:22

score 0 · Answer 3 · answered Nov 27 '13 at 14:37

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id   INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,user_to   INT NOT NULL
,user_from INT NOT NULL  
,content   CHAR(3) NOT NULL  
,dt DATE NOT NULL
);

INSERT INTO my_table VALUES
(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'); 

SELECT x.* 
  FROM my_table x 
  JOIN 
     ( SELECT LEAST(user_to,user_from) l
            , GREATEST(user_to,user_from) g
            , MAX(dt) max_dt FROM my_table 
        GROUP 
           BY LEAST(user_to,user_from)
            , GREATEST(user_to,user_from)
     ) y 
    ON y.l = LEAST(x.user_to,x.user_from) 
   AND y.g = GREATEST(x.user_to,x.user_from) 
   AND y.max_dt = x.dt 
 WHERE 1 IN (x.user_to,x.user_from);
+----+---------+-----------+---------+------------+
| id | user_to | user_from | content | dt         |
+----+---------+-----------+---------+------------+
|  3 |       3 |         1 | CBC     | 2013-11-07 |
|  6 |       5 |         1 | ABB     | 2013-11-10 |
|  8 |       1 |         2 | ACC     | 2013-11-12 |
+----+---------+-----------+---------+------------+

Selecting latest row of record with distinct parameter

3 Answers3