ID before current ID

[Kudose]

Hello all.

Is there an easy way (via MySQL or otherwise) to get the very first record before a given record? I know I could get them all in an ordered array, find the record given, then choose the array just before it, but that seems sort of inefficient.

i.e.

I have a table with an 'id' column that is auto_increment. I have a list ordered by 'id' ascending. What I am wanting to do is to create a "move up" and "move down" link. I was initially thinking just to increment or decrement the ID by one and swap the rows. This obviously wont work once an ID is skipped or deleted from the contiguous range.

Can someone help me figure out a way to do this still using auto_increment my id column?

TIA.

[Shrike]

Order the result set by the id and add a LIMIT -2, 1 (or vice versa). The ID should be used to identify the row, and auto-increment rows can't be changed afaik. Consider adding another column to specify a non-sequential order.

[Kudose]

I went the additional column route, but am having trouble figuring out what to do if a record is removed, breaking the order. I would need to update every row above it with a new position ID wouldnt I?

[Shrike]

Why would you need to do anything? In the sequence 1, 2, 3, 4 if you remove 2 then 3 still comes after 1. Unless your displaying the numbers I can't see a problem.

[Kudose]

Touche.

[edit]nvm[/edit]

[Kudose]

nvm