Argthtjtr

I have table as below:

+-----+---------------------+-----+-----+

| id  | date                | uid | sid |

+-----+---------------------+-----+-----+

| 454 | 2018-11-18 10:12:16 | 206 |  10 | 

| 456 | 2018-11-18 10:53:37 | 206 |  20 | O

| 467 | 2018-11-18 13:00:02 | 206 |  10 | C

| 469 | 2018-11-18 14:50:33 | 206 |  10 | 

| 452 | 2018-11-18 07:11:56 | 208 |  10 | 

| 470 | 2018-11-18 15:01:38 | 208 |  20 | 

| 455 | 2018-11-18 10:51:29 | 209 |  10 | 

| 458 | 2018-11-18 11:30:45 | 209 |  20 | O

| 459 | 2018-11-18 11:35:08 | 209 |  20 | 

| 460 | 2018-11-18 11:48:24 | 209 |  20 | 

| 462 | 2018-11-18 11:55:12 | 209 |  20 | 

| 464 | 2018-11-18 12:09:20 | 209 |  10 | C

| 465 | 2018-11-18 12:30:15 | 209 |  10 | 

| 468 | 2018-11-18 14:00:58 | 209 |  10 | 

| 471 | 2018-11-18 17:25:19 | 209 |  20 | O

| 472 | 2018-11-18 18:52:24 | 209 |  10 | C

| 453 | 2018-11-18 08:38:23 | 212 |  10 | 

| 457 | 2018-11-18 11:29:03 | 212 |  20 | O

| 461 | 2018-11-18 11:49:54 | 212 |  20 | 

| 463 | 2018-11-18 12:08:49 | 212 |  10 | C

| 466 | 2018-11-18 12:52:11 | 212 |  10 | 

+-----+---------------------+-----+-----+

i need the get the MIN(open) an MIN(clode) status
when sid = 20 is time open and sid = 10 is time close for a user
and i have more than multiple session per user

When i use the quote from [Retrieving the last record in each group - MySQL]

select 

t2.id, t2.date, t2.uid, t2.sid

from (

select t.*,

@r:= case when @g = t.uid

     then

          case when @sg = t.sid

          then 

            case when @sr <> t.date

            then  @r+1 

            else @r end 

          else 1 end 

      else 1 end r,

@g:= uid g,

@sg:= t.sid sg,

@sr:= t.date sr

from messages t

cross join (select @g:=null,@sg:=null,@r:=null) t1

order by t.uid,t.sid,t.date

  ) t2

where t2.r = 1 and sid = 20 or t2.r = 2 and sid = 10

order by uid,date

i get

+-----+---------------------+-----+----+

| id  | date                | uid |sid |

+-----+---------------------+-----+----+

| 456 | 2018-11-18 10:53:37 | 206 | 20 |

| 467 | 2018-11-18 13:00:02 | 206 | 10 |

| 470 | 2018-11-18 15:01:38 | 208 | 20 |

| 458 | 2018-11-18 11:30:45 | 209 | 20 |

| 464 | 2018-11-18 12:09:20 | 209 | 10 |

| 457 | 2018-11-18 11:29:03 | 212 | 20 |

| 463 | 2018-11-18 12:08:49 | 212 | 10 |

+-----+---------------------+-----+----+

the question is how i can get this?

+-----+---------------------+-----+----+

| id  | date                | uid |sid |

+-----+---------------------+-----+----+

| 456 | 2018-11-18 10:53:37 | 206 | 20 |

| 467 | 2018-11-18 13:00:02 | 206 | 10 |

| 458 | 2018-11-18 11:30:45 | 209 | 20 |

| 464 | 2018-11-18 12:09:20 | 209 | 10 |

| 471 | 2018-11-18 17:25:19 | 209 | 20 |

| 472 | 2018-11-18 19:05:38 | 209 | 10 |

| 457 | 2018-11-18 11:29:03 | 212 | 20 |

| 463 | 2018-11-18 12:08:49 | 212 | 10 |

+-----+---------------------+-----+----+

Assuming that your id values increase along with increasing date values (or if you don't really care about the id values), and you don't mind getting the open and close times in the same row, this query will give you the results you want without using variables, which can be unreliable:

SELECT MIN(m1.id) AS m1_id

     , MIN(m1.uid) AS uid

     , MIN(m1.sid) AS sid

     , MIN(m1.date) AS m1_date

     , MIN(m2.id) AS m2_id

     , MIN(m2.sid) AS m2_sid

     , m2.date AS m2_date

FROM messages m1

LEFT JOIN messages m2 ON m2.uid = m1.uid AND m2.sid != m1.sid AND m2.sid = 10 AND

    m2.date = (SELECT MIN(date)

               FROM messages m3 

               WHERE m3.uid = m2.uid AND m3.sid = 10 AND m3.date > m1.date)

WHERE m2.id IS NOT NULL

GROUP BY m2.date

Output:

m1_id   uid     sid     m1_date                 m2_id   m2_sid  m2_date

456     206     20      2018-11-18 10:53:37     467     10      2018-11-18 13:00:02

458     209     20      2018-11-18 11:30:45     464     10      2018-11-18 12:09:20

471     209     20      2018-11-18 17:25:19     472     10      2018-11-19 18:52:24

457     212     20      2018-11-18 11:29:03     463     10      2018-11-18 12:08:49

Demo on dbfiddle