Previous / Next for alphabetical order

[letimati]

Hi I would like to get the next and previous records when i am ordering alphabetically.

So i have the following

id name
1 john
2 simon
3 luke

I am on the record luke and i want to get the next one and the previous one in alphabetical order. So my sql query would give me back john then luke.

It is easy when the value is numerical cause i would just use two statements saying give me a record greater than (less than) limit to 1 but for the life of me i cant think how i would do this aphabetically without pulling them all out.

thanks in advance for any time you spend reading this.

 

[r937]

select id
     , ( select min(name)
           from daTable
          where name > T.name ) as nextname 
     , ( select max(name)
           from daTable
          where name < T.name ) as prevname 
  from daTable as T
 where name = 'luke'

r937.com | rudy.ca

 

[letimati]

Thanks so much i would never have know how to do this.

can anyone recommended a good sql book to get into learning this kind of stuff?

 

[letimati]

Would it be difficult to return the id's rather than the names?

 

[r937]

you will need another level of subquery

select id
     , ( select id
           from daTable
          where name =
                ( select min(name)
                    from daTable
                   where name > T.name ) ) as nextid
...

r937.com | rudy.ca