MySQL next entry after certain condition I have a table with history of transfers for codes.
These can travel between different locations like (X,Y,Z,etc.)

This is an example table of transfers for code: 102263

mysql&gt; SELECT datetime, code, location FROM table WHERE code LIKE '102263'

+-----datetime-----+----code----+----location----+
|  5.1.2017 12:54  |  102263    |       X        |  
|  2.2.2017 8:42   |  102263    |       Y        | &lt;--- first transfer from X to Y
|  2.3.2017 12:04  |  102263    |       Z        |
|  6.3.2017 13:34  |  102263    |       Y        |
|  7.3.2017 7:57   |  102263    |       Z        |
|  7.3.2017 18:37  |  102263    |       Y        |
|  9.3.2017 17:47  |  102263    |       Z        |
|  13.3.2017 7:56  |  102263    |       Y        |
|  21.3.2017 11:44 |  102263    |       Z        | 
|  22.3.2017 20:28 |  102263    |       Y        |
|  24.3.2017 13:07 |  102263    |       X        | 
|  25.3.2017 14:01 |  102263    |       Y        | &lt;--- second transfer from X to Y
|  26.3.2017 8:14  |  102263    |       Z        |
|  27.3.2017 9:52  |  102263    |       Y        |
|  3.4.2017 10:02  |  102263    |       X        | &lt;--- beginning of third transfer
+------------------+------------+----------------+


I need to select data between specific two locations(X and Y), but only the first transfer from X to Y then the next transfer between X and Y, etc. ignoring all the entries between (Z and Y). 
So the result should be:

+-----datetime-----+----code----+----location----+
| 5.1.2017 12:54   |   102263   |        X       |
| 2.2.2017 8:42    |   102263   |        Y       | &lt;--- first transfer from X to Y
| 24.3.2017 13:07  |   102263   |        X       |
| 25.3.2017 14:01  |   102263   |        Y       | &lt;--- second transfer from X to Y
| 3.4.2017 10:02   |   102263   |        X       | &lt;--- beginning of third transfer
+------------------+------------+----------------+


I tried to limit to locations to X and Y, but I also need to skip the Y dates that are not directly after X

mysql&gt; SELECT datetime, code, location FROM table WHERE code LIKE '102263' AND (location LIKE 'X' OR location LIKE 'Y')

+-----datetime-----+----code----+----location----+
|  5.1.2017 12:54  |  102263    |       X        |  
|  2.2.2017 8:42   |  102263    |       Y        | &lt;--- first transfer from X to Y
|  6.3.2017 13:34  |  102263    |       Y        | &lt;--- skip
|  7.3.2017 18:37  |  102263    |       Y        | &lt;--- skip
|  13.3.2017 7:56  |  102263    |       Y        | &lt;--- skip
|  22.3.2017 20:28 |  102263    |       Y        | &lt;--- skip
|  24.3.2017 13:07 |  102263    |       X        | 
|  25.3.2017 14:01 |  102263    |       Y        | &lt;--- second transfer from X to Y
|  27.3.2017 9:52  |  102263    |       Y        | &lt;--- skip
|  3.4.2017 10:02  |  102263    |       X        | &lt;--- beginning of third transfer
+------------------+------------+----------------+


Thank you very much.
Well, MySQL isn't a specialty of mine, so there may be a better way of doing this. However, after checking to see how/if MySQL implemented various concepts (like SQL Server's ROW_NUMBER or LEAD functions), this appears to work.

This uses a trick, where the value of a variable is updated with each row, in sequence, and both the old and new values of the variable are included as columns. This lets us pick up the location from the previous row in the current row. (Note that I am not familiar enough with MySQL to know if this is a deliberate feature; in SQL Server, something similar works - but is not documented as a feature, and is generally thought of as something that could stop working with the next version.)

With that trick, we:


Limit our records to those that have one of the two locations we're interested in;
add the previous row's location to the current row; and
select the desired fields from the rows where the previous row's location is different from that of the current row.


Here's the code:

CREATE TABLE WhenWhatWhere (`when` datetime, `code` int, `location` char(1), CONSTRAINT PRIMARY KEY (`code`,`when`));

INSERT INTO WhenWhatWhere
VALUES ('2017-01-05 12:54', 102263, 'X')
      ,('2017-02-02 8:42', 102263, 'Y')
      ,('2017-03-02 12:04', 102263, 'Z')
      ,('2017-03-06 13:34', 102263, 'Y')
      ,('2017-03-07 7:57', 102263, 'Z')
      ,('2017-03-07 18:37', 102263, 'Y')
      ,('2017-03-09 17:47', 102263, 'Z')
      ,('2017-03-13 7:56', 102263, 'Y')
      ,('2017-03-21 11:44', 102263, 'Z')
      ,('2017-03-22 20:28', 102263, 'Y')
      ,('2017-03-24 13:07', 102263, 'X')
      ,('2017-03-25 14:01', 102263, 'Y')
      ,('2017-03-26 8:14', 102263, 'Z')
      ,('2017-03-27 9:52', 102263, 'Y')
      ,('2017-04-03 10:02', 102263, 'X')
;

SET @rownum = 0;
SET @loc1 = 'X';
SET @loc2 = 'Y';
SET @lastloc = '';

-- 
-- Select only the rows where the location changed from the previous row.
SELECT `when`, `code`, `location`
  FROM ( -- Add the location from the previous row; must be sorted to work
        SELECT `when`, `code`, @lastloc `lastloc`, (@lastloc := `location`) `location`
          FROM ( -- Select just the rows that deal with our two locations
                SELECT `when`, `code`, `location`
                  FROM WhenWhatWhere
                 WHERE `location` = @loc1 OR `location` = @loc2
                  -- add additional restrictions (like date range or specific code) here
               ) loc
         ORDER BY `when`
       ) comploc
 WHERE `lastloc` &lt;&gt; `location`
 ORDER BY `when`
;


It worked when I ran it at sqlfiddle.com.