1

It's possible that this is really easy and it's just Friday brain problems but I'm struggling here...

I have a MySQL table which contains text entries such as : 

Elephant
Apple
Dog
Carrot
Banana

I pull one row, ie Dog. I now want to pull the previous alphabetical row and the following alphabetical row, ie Carrot & Elephant

So.. 

SELECT text FROM table WHERE text >= 'Dog' ORDER BY text LIMIT 1

Gives me the next alphabetical row. However, 

SELECT text FROM table WHERE text <= 'Dog' ORDER BY text LIMIT 1

perhaps obviously gives me the first alphabetical row of the table. I figured I could get table position and then order alpahbetically and do a LIMIT x-1,1 to get previous but that seems awful clunky.

I'm sure I'm missing something obvious but my head hurts.

Ideas ?

Phil
  • 11
  • 2
  • wouldn't it be easier to read the whole table into something like a linked list and then get the next and previous item to the one your afer? – Tarski Nov 12 '10 at 11:09
  • Have a look at my answer to a previous similar question: http://stackoverflow.com/questions/1259458/sql-pulling-a-row-for-next-or-previous-row-of-a-current-row/2827609#2827609 – Mike Nov 12 '10 at 12:04

2 Answers2

3

ORDER BY text DESC

Hammerite
  • 21,755
  • 6
  • 70
  • 91
0

Here is the cleanest and most efficient way: To get next:

 select min(name) people where name > 'Dog';

To get previous:

 select max(name) people where name < 'Dog';
Handsome Nerd
  • 17,114
  • 22
  • 95
  • 173