How get next row and previous row for now row in table

Question

I have table News.

For example i get row where NewsIdn = '164955':

SELECT * FROM News WHERE NewsIdn = '164955'

Previous row have NewsIdn = '270085' and next row have NewsIdn = '324955'.

Tell me please how get previous NewsIdn and next NewsIdn with help mysql query?

Do you mean something like this? http://stackoverflow.com/questions/1259458/sql-pulling-a-row-for-next-or-previous-row-of-a-current-row — user2183923, Jan 14 '14 at 18:47

BWS · Accepted Answer · 2014-01-14T19:25:37.740

2

Maybe something like this:

Main query:

SELECT * FROM News WHERE NewsIdn = '164955';

select * from News WHERE ID < 
    (select ID from News where NewsIdn = '164955') Order by ID DESC Limit 1;

select * from News WHERE ID > 
    (select ID from News where NewsIdn = '164955') Order by ID Limit 1;

edited Jan 14 '14 at 19:25

answered Jan 14 '14 at 18:50

BWS

Why do you need the sub query? – Andrew McGivery Jan 14 '14 at 18:54
Cant really guarantee the order of the select list will be the same each time ... select the ID less than the original one will always get the 'correct' row. – BWS Jan 14 '14 at 18:56
I just realized that NewsIdn is not the ID of the row. Your answer would be indeed correct. – Andrew McGivery Jan 14 '14 at 18:57
@learner -- yes -- i now see. I added a DESC to the query for previous. Now works. http://sqlfiddle.com/#!2/a926a/13 – BWS Jan 14 '14 at 19:26

score 0 · Answer 2 · answered Jan 14 '14 at 18:46

0

Try this:

SELECT * FROM News WHERE NewsIdn < '164955' ORDER BY NewsIdn DESC LIMIT 1

SELECT * FROM News WHERE NewsIdn > '164955' ORDER BY NewsIdn LIMIT 1

answered Jan 14 '14 at 18:46

Andrew McGivery