MySQL query - Before and after rows of a custom ordered results

I've seen other questions similar in StackOverflow, but all of them are based in a auto-increment ID, but I don't have that.

I have a query like:

  SELECT field_a, 
         field_b 
    from table
   where field_m = '100' 
     and field_n = '200'
order by field_x

That results in this

field_a    field_b
-------------------
john       12     
marty      7     
peter      2     
carl       9     
mark       11     
bob        10     
neil       1     
louis      14     

So, what I want is to complete the original query and with ONE QUERY to take the record BEFORE and AFTER one of them ... let's say "carl", but it's important that in each case is different, I mean, other times would need before and after of "bob" ...

So, let's say "carl" ... I need to create a ONLY SQL QUERY in which I use the order by field_x descr开发者_开发百科ibed , and take the before and after rows when field_a='carl'

It would be rather heavy on big tables, but you can use ranking and do join twice to have previous and next record and then just use where to filter it.

SET @rank_prev = 0;
SET @rank_cur = 0; 
SET @rank_next = 0;
SELECT
    prev.field_a as prev_a,
    prev.field_b as prev_b,
    next.field_a as next_a,
    next.field_b as next_b
FROM
 (  
  SELECT
      @rank_cur:=@rank_cur+1 AS rank,
      field_a, 
      field_b  
  FROM dd
  WHERE field_m = '100' 
    AND field_n = '200'
  ORDER BY field_x
 ) as cur
INNER JOIN
 (
  SELECT
      @rank_prev:=@rank_prev+1 AS rank,
      field_a, 
      field_b  
  FROM dd
  WHERE field_m = '100' 
    AND field_n = '200'
  ORDER BY field_x
 ) as prev
 ON prev.rank + 1 = cur.rank
INNER JOIN
 (
  SELECT
      @rank_next:=@rank_next+1 AS rank,
      field_a, 
      field_b  
  FROM dd
  WHERE field_m = '100' 
    AND field_n = '200'
  ORDER BY field_x
 ) as next
 ON cur.rank+1 = next.rank
 WHERE cur.field_a = 'carl';

Works on MySQL

You can use one query, but you need to UNION between two sets to get the results merged together

SELECT * FROM
(
SELECT b.*
from tbl a
inner join tbl b on
      b.field_m = '100'
  and b.field_n = '200'
where a.field_m = '100'
  and a.field_n = '200'
  and a.field_a = 'carl'
  and b.field_x <= a.field_x  # comes before a sorted on x
order by b.field_x DESC
limit 2
) A
UNION
SELECT * FROM
(
SELECT b.*
from tbl a
inner join tbl b on
      b.field_m = '100'
  and b.field_n = '200'
where a.field_m = '100'
  and a.field_n = '200'
  and a.field_a = 'carl'
  and b.field_x >= a.field_x  # comes after a sorted on x
order by b.field_x ASC
limit 2
) B

Note: This includes 'carl' itself. UNION takes care of removing the 2nd 'carl'.

Performance - an index should be created on at least (field_m, field_n), better if it is (field_m, field_n, field_x) to make this query perform reasonably. As long as field_m + field_n cut the table down to size, the performance is

(size after filter m/n) x (size after filter m/n)  // triangular
x2

The way this works is that it crosses the set to itself, where a is anchored at "carl" and b keeps only the rows that are positionally either before (set 1) or after (set 2). Ordering these properly then taking the LIMIT 2 will include 'carl' as well as one other (unless that is also 'carl' when duplicates are allowed).