[RESOLVED] Pull 'Previous' & 'Next' row with specific WHERE

[curb]

I have a database with the following columns: id,name,age,subject

Example Data:
1,john,12,english
2,jane,11,math
3,josh,13,science
4,jenn,11,english
5,jones,12,math
6,jill,11,english

If I have Jenn(#4 english) currently pulled out, how do I also pull out the 'previous' and 'next' student that has english? (John as Previous and Jill as Next)

[kakki]

maybe like this:
to get the previous result

SELECT MAX(id) FROM students WHERE id < 4

to get the next result

SELECT MIN(id) FROM students WHERE id > 4

into one query:

SELECT * FROM students WHERE id IN
      ( SELECT MAX(id) FROM students WHERE id < 4 )
OR id in 
      ( SELECT MIN(id) FROM students WHERE id > 4 )     

[curb]

Thanks kakki. I added the AND subject as well to point me to the english I needed.

SELECT * FROM students WHERE id<4 AND subject='english' ORDER BY id DESC LIMIT 1