mySQL select records based on difference to previously selected record

Question

This is difficult to explain so please bear with me: I'm trying to construct a mySQL query that will select the first record but then will not select another record until a certain requirement of difference has been met by an integer column.

e.g., if I the required difference was 10, the marked results should be selected from the following records:

IntCol
-------------------
0 *
1 
2
3
8
10 *
15
39 *
48
50 *
59
81 *
82
84
88
91 *
100

So basically, a record will only be selected if the previously selected record is at least 10 less than the current one. Nothing I have tried has been even remotely successful.

score 2 · Accepted Answer · answered Oct 01 '15 at 04:04

Yes it is possible with a query variable:

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE Table1
    (`IntCol` int)
;

INSERT INTO Table1
    (`IntCol`)
VALUES
    (0),
    (1),
    (2),
    (3),
    (8),
    (10),
    (15),
    (39),
    (48),
    (50),
    (59),
    (81),
    (82),
    (84),
    (88),
    (91),
    (100)
;

Query 1:

select intcol FROM
(
  select intcol, Case when intcol - @i >= 10 then @i := intcol else @i end as correctCol
  from Table1, (SELECT @i := 0) r
  order by intcol
) a
WHERE intcol = correctCol
order by intcol

Results:

| intcol |
|--------|
|      0 |
|     10 |
|     39 |
|     50 |
|     81 |
|     91 |

Just applied it to my actual application. Works perfectly. Thanks very much. — , Oct 01 '15 at 07:43

mySQL select records based on difference to previously selected record

1 Answers1