MySQL select before after row

mysql row surround
bosniamaj picture bosniamaj · Mar 23, 2012 · Viewed 21.2k times · Source

This is the example table:

Column             | 1st record | 2nd record | 3rd record | 4th record | etc<br />
id (primary)       | 1          | 5          | 8          | 12         | etc<br />
name               | name 1     | name 2     | name 3     | name 4     | etc<br />
date               | date 1     | date 2     | date 3     | date 4     | etc<br />
callValue (unique) | val1       | val2       | val3       | val4       | etc

I select one row that is the data to show (for example: row with callValue: val3). But I cannot find a solution for this:
I need to select previous and next row. So, in this example, I need to get data from rows callValue: val4 and callValue: val2, or id: 5 and id: 12.

It cannot be done with id=id+-1 because id doesn't have to be continuous because of deleting rows.

Answer

Andrey Gurinov picture Andrey Gurinov · Mar 23, 2012

Try this:

select * from test where callValue = 'val3'  
union all  
(select * from test where callValue < 'val3' order by id desc limit 1) 
union all  
(select * from test where callValue > 'val3' order by id asc limit 1)

or

select * from test where id = 8
union all  
(select * from test where id < 8 order by id desc limit 1) 
union all  
(select * from test where id > 8 order by id asc limit 1)

Related questions

How do I add indices to MySQL tables?

I've got a very large MySQL table with about 150,000 rows of data. Currently, when I try and run SELECT * FROM table WHERE id = '1'; the code runs fine as the ID field is the primary index. However, for a …

Read More
In MySQL, can I copy one row to insert into the same table?

insert into table select * from table where primarykey=1 I just want to copy one row to insert into the same table (i.e., I want to duplicate an existing row in the table) but I want to do this without …

Read More
How to configure MySQL 5.6 LONGBLOB for large binary data

Before I ask my question a little background: I'm doing the Data Export/Import using the MySQL Workbench 6.1 of a MySQL 5.5 database from one machine to a 5.6 on another. both machines are ubuntu one 32-bit the other 64-bit. I dump …

Read More