I have a table that is something like this
id | names | value
1 Vicky 43
2 Erica 23
3 Rueben 33
4 Bob 54
5 Chris 60
Then I set them in order according to their value. Now the table looks like this.
id | names | value
5 Chris 60
4 Bob 54
1 Vicky 43
3 Rueben 33
2 Erica 23
Now the starting point is id 5 which has a name of Chris and a value of 60. My goal is, to get the next row which has an id of 4 and name of Bob and a value of 54.
You just need to limit the resultset:
SELECT * from table
ORDER BY value DESC
LIMIT 1, 1
Output:
| ID | NAMES | VALUE |
|----|-------|-------|
| 4 | Bob | 54 |
Fiddle here.
The LIMIT basically works this way: the first number sets the starting point (being 0 the minimal value) and the second number the amount of items to fetch (in this case only one).
Edit:
A different way of understanding the question would be: Given a value for a particular field (EG: id field with value of 5)... what would be the previous record? As we have the id 4 in the data we should return that one.
That could be accomplished this way:
SELECT * from t
WHERE id < 5
ORDER BY id DESC
LIMIT 1
Fiddle here.
This way you can traverse the results in both orders (ASC and DESC) and also get both the next or previous (> or <) rows.
sql server:
with temp as
(
SELECT ROW_NUMBER() OVER (ORDER BY value desc) AS ROWID, * FROM table_name
)
SELECT * from temp where ROWID=2
mysql:
SELECT * from table
ORDER BY value DESC
LIMIT 1, 1
I get the feeling that this is a PHP related question?
If that's so, then you can use PHP's mysql or mysqli_fetch functions to get what you want... along with a loop
This is your basic loop-through-a-mysql-query
$sql = mysql_query( "SELECT * from table ORDER BY value DESC" );
while ( $r = mysql_fetch_array( $sql ) ) {
echo $r['value'] . "<br />
";
}
If you want to have them all at your disposal and be able to call either one of them at will, you will need to store the data in an accessible array, like so
$sql = mysql_query( "SELECT * from table ORDER BY value DESC" );
$theData = array();
while ( $r = mysql_fetch_array( $sql ) ) {
$theData[] = $r['value'];
}
Then, to access the SECOND value, use this
echo $theData[1];
If your current ID is for example 4 then
Next:
select * from foo where id = (select min(id) from foo where id > 4)
previous:
select * from foo where id = (select max(id) from foo where id < 4)