MySQL Query Help?

Suppose I have a table with an id column.

I want to do a MySQL Query looking at all the data in the table and seeing if it meets either of two conditions. If it meets the first condition (called X), then I want to find the ID when the next condition is met (Y) without any overlap. So basically a pattern will merge X then Y.

No overlap means that for instance if the ID where condition X was met was 6 and the ID where condition Y was met was 12. I don't want the next X condition to be between 6 and 12 even if an X condition exists.

I am confused how to set up a query like this. I was thinking I could use a join of the same table and then have a where clause conditioning the second id being greater than the first but that didn't work.

This is originally query I had

SELECT x.id AS 'x_id', x.data AS 'x_data', y.id AS 'y_id', y.data AS 'y_data' 
FROM Table1 AS x, Table1 AS y 
WHERE y.id > x.id AND y.id-x.id >= 5 
AND Y Conditions AND X Conditions

id1 where x was met, id 2 where y was met where data is some random number between 0 and 1. And if Condition X (for instance X <.5 is met then I want the ID) then I want the following ID where Condition Y is met (for instance Y > .6). So basically the output is two different IDs from the same table based on two conditions.

I want a pattern to emerge where it goes X then Y then X then Y with no overlap in IDs between X and Y

6, Data, 15, Data
开发者_开发问答6, Data, 21, Data
6, Data 23, Data
6, Data , 27, Data
9, Data, 15, Data
9, Data, 21, Data

Ideally this is the format of the resultset that I want id-x data id-y data

Help is greatly appreciated.

First a view to find all valid (X, Y pairs) patterns (with overlapping):

CREATE VIEW valid AS
( SELECT x.id AS xid
       , x.datta AS xdata
       , ( SELECT MIN(y.id)
           FROM Table1 y 
           WHERE y.id - xid >= 5
           AND (Y conditions)      --Y conditions here
         ) AS yid
       , ( SELECT datta
           FROM Table1 y
           WHERE y.id = yid
         ) AS ydata
  FROM Table1 AS x
  WHERE (X conditions)      --X conditions here
);

(Check with):

SELECT *
FROM valid
;

Create the procedure that finds patterns without overlapping:

DELIMITER $$
CREATE PROCEDURE FindPatterns()
BEGIN
  SET  @lastgoody := -9999;
  SET @dummy := FALSE;

  SELECT xid, xdata, yid, ydata, testy, test FROM
    ( select xid
           , xdata
           , yid
           , ydata
           , @dummy := IF(q.xid > @lastgoody, TRUE, FALSE) as test
           , @lastgoody := IF(@dummy, q.yid, @lastgoody) as testy
      from valid q
      order by xid
    ) as p
  WHERE test ;
END$$
DELIMITER ;

You can then:

CALL FindPatterns;