Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL select before after row

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.

like image 761
bosniamaj Avatar asked Mar 23 '12 08:03

bosniamaj


People also ask

How do I select specific rows in SQL?

To select rows using selection symbols for character or graphic data, use the LIKE keyword in a WHERE clause, and the underscore and percent sign as selection symbols. You can create multiple row conditions, and use the AND, OR, or IN keywords to connect the conditions.

How do I find the next record in MySQL?

You can use UNION to get the previous and next record in MySQL. Insert some records in the table using insert command. Display all records from the table using select statement.


2 Answers

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) 
like image 94
Andrey Gurinov Avatar answered Sep 30 '22 09:09

Andrey Gurinov


Once you have the id 8, you should be able to do a variation on:

select * from mytable
where id < 8
order by id desc
limit 1

and:

select * from mytable
where id > 8
order by id asc
limit 1

for the previous and next record.

like image 22
paxdiablo Avatar answered Sep 30 '22 09:09

paxdiablo