如何获取sql中的下一行

Question

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.

Answer 1

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.