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 | Danebut my table has gaps and often its more like
ID | name | surname
2 | John | doe
7 | Jane | DaneSo 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)
精彩评论