1

MYSQL query to fetch the neighbour rows along with the present(id) given condition

like

ID | BOOK     |
--------------
1  | xyzbooks |

2  | pqrbooks |

3  | dnsaddds |

4  | dfbsakjf |

5  | dbsadbdd |

6  | tyrbooks |

7  | sfdffdsf |

8  | yuibooks |

I am on id 3 (dnsaddds which i could get from url) and i searched for books . Now I need to fetch the row data of 3rd row, and next and previous results of searched text (books)

RESULT EXPECTED is as follows

ID | BOOK     | AUTHOR | DATE
---------------------------
2  | pqrbooks | fbsdjf | 2013

3  | dnsaddds | fdsfbs | 2012

6  | tyrbooks | fdsdff | 2011

Looking for a single query with out unions etc..

Naveen Kumar
  • 39
  • 4

2 Answers2

2

This should work:

select id, book, author, date
from t
where id = 3
      OR id = (SELECT MAX(id) FROM t WHERE id < 3)
      OR id = (SELECT MIN(id) FROM t WHERE id > 3)
Tom
  • 6,593
  • 3
  • 21
  • 42
0

You can do this by unioning together the three rows that you want:

(select id, book, author, date
 from t
 where id = 3
) union all
(select id, book, author, date
 from t
 where id < 3 and book like '%books%'
 order by id desc
 limit 1
) union all
(select id, book, author, date
 from t
 where id > 3 and book like '%books%'
 order by id
 limit 1
)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786