previous and next row for given row with specific ORDER

[micah1701]

The row ID's in my table are sequencial but the values of the column I want to sort by are not alphabetical.

it other words, my table looks like:

ID   NAME
1    Bob
2    Andy
3    Cletus
4    Audrey
5    Dustin
6    Elma

 

To query them in order is simple enough: 

SELECT `name` FROM `table` ORDER BY `name` 

will return:  Andy, Audrey, Bob, Cletus, Dustin, Elma

 

my problem is that I need to list the previous and next name with a given ID.

 

So, if I am talking about "Cletus" (SELECT name FROM table WHERE id = 3) I want to find the names of the persons listed alphabetically before and after him.  (eg, "Bob" and "Dustin")

 

I know I could do this programmatically with PHP, spitting out an array of all the names in order then finding the one I want and grabbing the names that come before and after it in the array but my list of names may be pretty big and it seems like it should be possible to do this in the query.

 

Any thoughts?

[kickstart]

Hi

 

Can be done like this, but I am sure there must be a better way to do it.

 

(SELECT Id, Name
FROM name
WHERE Id = 3)
UNION
(SELECT Id, Name
FROM name
WHERE Name > (SELECT Name FROM name WHERE Id = 3)
ORDER BY Name
LIMIT 0,1)
UNION
(SELECT Id, Name
FROM name
WHERE Name < (SELECT Name FROM name WHERE Id = 3)
ORDER BY Name DESC
LIMIT 0,1)
ORDER BY Name

 

Hopefully someone will come up with a more efficient way of doing it.

 

All the best

 

Keith

[micah1701]

That works good enough for me Kickstart! 

 

Thanks