CODEWITHSUNDEEP
phpmysqlsql
ILikebanana
ILikebanana

Reputation: 109

How to get the next row in sql

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.

Upvotes: 1

Views: 13014

Answers (4)

Ash Mortazavi
Ash Mortazavi

Reputation: 21

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)

Upvotes: 2

dockeryZ
dockeryZ

Reputation: 3981

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 />\n";
}

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];

Upvotes: 0

user3059794
user3059794

Reputation: 41

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

Upvotes: 1

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

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.

Upvotes: 7

Related Questions