开发者

MySQL select a row preceding the row with ID = X

thi开发者_运维百科s is actually a sorting question almost solved thanks to other posts I've found here on StackOverflow.

basically i'm trying to bumb a row up , no need to insert it somewhere in the middle of the table and change all the following rows id's just one place up (replace order id's with previous). I've found a solution for this in another post WHEN the id's are known for example.

ID | name | surname

1 | John | doe

2 | Jane | Dane

but my table has gaps and often its more like

ID | name | surname

2 | John | doe

7 | Jane | Dane

So i can't really rely on subtracting the id by 1 and replacing row 7 with the row number 7-1.

Is it somehow possible to swwitch the row id X with the one before without knowing the other id ?

is it possible to do it SQL only ? i have an idea of php-ing it but it seems alot like a fix not a solution.


This would work in SQL Server, I'm assuming in MySQL too:

SELECT *
FROM MyTable MT
...
WHERE ID = (SELECT MAX(ID) FROM MyTable MT2 WHERE ID < MT.ID)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜