CODEWITHSUNDEEP
mysql
Hernán Eche
Hernán Eche

Reputation: 6898

Select range of data, from first to last appeareance

From a table like this

----------------------------------
| User | TimeStamp         | Kind|
----------------------------------
| bob  |2015-01-19 03:14:16| err |
| bob  |2015-01-20 02:07:18|  ok | <-- from here |
| bob  |2015-01-21 12:07:18| err |               | 
| bob  |2015-01-22 12:08:26|  ok |               |
| bob  |2015-01-22 13:40:04| err | <-- to here   |
| bob  |2015-01-23 20:07:00|  ok |
| tom  |2015-01-19 03:14:16| err |
| tom  |2015-01-20 02:07:18|  ok | <-- from here|
| tom  |2015-01-22 13:40:04| err | <-- to here  |
| tom  |2015-01-23 20:07:00|  ok |

How can I get from the first row were Kind=ok to the last with Kind=err

expected result:

----------------------------------
| User | TimeStamp         | Kind|
----------------------------------
| bob  |2015-01-20 02:07:18|  ok |
| bob  |2015-01-21 12:07:18| err |
| bob  |2015-01-22 12:08:26|  ok |
| bob  |2015-01-22 13:40:04| err |
| tom  |2015-01-20 02:07:18|  ok | 
| tom  |2015-01-22 13:40:04| err |

There is no unique id in the table but timestamps are ordered

EDIT: There can be multiple users

Upvotes: 0

Views: 47

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use the following query:

SELECT User, TimeStamp, Kind
FROM mytable
WHERE TimeStamp BETWEEN (SELECT MIN(TimeStamp)
                         FROM mytable
                         WHERE Kind = 'ok')
                AND
                        (SELECT MAX(TimeStamp)
                         FROM mytable
                         WHERE Kind = 'err')

If you want to apply the above query to data where more than one User values are involved, then you have to use correlation in the subqueries, so as to get the MIN and MAX per user:

SELECT User, TimeStamp, Kind
FROM mytable AS t1
WHERE TimeStamp BETWEEN (SELECT MIN(t2.TimeStamp)
                         FROM mytable AS t2
                         WHERE t1.User = t2.User AND t2.Kind = 'ok')
                AND
                        (SELECT MAX(t3.TimeStamp)
                         FROM mytable AS t3
                         WHERE t1.User = t3.User AND t3.Kind = 'err')

Alternatively you can use INNER JOIN:

SELECT t1.User, t1.TimeStamp, t1.Kind
FROM mytable AS t1
INNER JOIN (
   SELECT User, 
          MIN(CASE WHEN Kind = 'ok' THEN TimeStamp END) AS ok_time,
          MAX(CASE WHEN Kind = 'err' THEN TimeStamp END) AS err_time
   FROM mytable
   GROUP BY User
) AS t2 ON t1.User = t2.User 
WHERE t1.TimeStamp BETWEEN t2.ok_time AND t2.err_time

Upvotes: 2

Related Questions