Tuesday, 2 June 2015

Mysql: Select N rows before and after the matching row

Table definition:
CREATE TABLE languages(
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  PRIMARY KEY (language_id)
);

Populate table:
INSERT INTO languages VALUES
  (1, 'English'),
  (5, 'Italian'),
  (10, 'Japanese'),
  (11, 'Mandarin'),
  (21, 'French'),
  (25, 'German'),
  (30, 'Spanish'),
  (32, 'Turkish');

Find the matching row and its nearest records - 2 before and 2 after:
SELECT id, name FROM (
  SELECT
    l.*,
    @i:=@i + 1 rank,
    @match:=IF(l.name = 'French', @i, @match)
  FROM
    languages l,
    (SELECT @i:=0, @match:=0) vars
  ORDER BY
    l.id
) t
WHERE
  @match >= rank - 2 AND @match <= rank + 2;
+----+----------+
| id | name     |
+----+----------+
| 10 | Japanese |
| 11 | Mandarin |
| 21 | French   |
| 25 | German   |
| 30 | Spanish  |
+----+----------+

0 comments:

Post a Comment