MIN() DateTime record Retrieving - MySQL

Question

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 |
+-----+---------------------+-----+----+

score 0 · Accepted Answer · answered Nov 23 '18 at 03: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

MIN() DateTime record Retrieving - MySQL

1 Answers1