Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » Select N rows before and after the matching row

Select N rows before and after the matching row

Author: rootsql Date: 16-11-2011, 22:35

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  |
+----+----------+
Dear visitor, you went to the site as unregistered user.
We recommend you to Register or Sign In.

Comments:

Leave a comment
Information
Members of Guests cannot leave comments.