SQLite selects the next and previous row based on where clause

Question

I want to be able to get next and previous row to use SQLite.

 id statusid date 168 1 2010-01-28 16:42:27.167 164 1 2010-01-28 08:52:07.207 163 1 2010-01-28 08:51:20.813 161 1 2010-01-28 07:10:35.373 160 1 2010-01-27 16:09:32.550 46 2 2010-01-30 17:13:45.750 145 2 2010-01-30 17:13:42.607 142 2 2010-01-30 16:11:58.020 140 2 2010-01-30 15:45:00.543 

For instance:

Given id 46 , I would like to return identifiers 160 (previous) and 145 (next)

Given id 160 , I would like to return ids 161 (previous) and 46 (next), etc ...

Remember that data is ordered by statusId then dateCreated DESC and HAS to work using SQLite.

 select * from @t order by statusId, dateCreated desc 

Test data created on sql server ...

 set nocount on; set dateformat ymd; declare @t table(id int, statusId int, dateCreated datetime) insert into @t select 168,1,'2010-01-28 16:42:27.167' union select 164,1,'2010-01-28 08:52:07.207' union select 163,1,'2010-01-28 08:51:20.813' union select 161,1,'2010-01-28 07:10:35.373' union select 160,1,'2010-01-27 16:09:32.550' union select 46,2,'2010-01-30 17:13:45.750' union select 145,2,'2010-01-30 17:13:42.607' union select 142,2,'2010-01-30 16:11:58.020' union select 140,2,'2010-01-30 15:45:00.543' 

Using SQL Server 2005+, it will be pretty trivial!

EDIT:

Here is the same test script data, but for SQLite , which are the focus of the question.

 create table t (id int, statusId int, dateCreated datetime); insert into t select 168,1,'2010-01-28 16:42:27.167' union select 164,1,'2010-01-28 08:52:07.207' union select 163,1,'2010-01-28 08:51:20.813' union select 161,1,'2010-01-28 07:10:35.373' union select 160,1,'2010-01-27 16:09:32.550' union select 46,2,'2010-01-30 17:13:45.750' union select 145,2,'2010-01-30 17:13:42.607' union select 142,2,'2010-01-30 16:11:58.020' union select 140,2,'2010-01-30 15:45:00.543'; 

EDIT 2 Note that the data is not a good example, so I changed id 146 to 46

Answer 1

This problem is much more complicated than at the beginning. Two field orders must be processed separately, and then combined with the union and obtain the corresponding result. To get both the previous and the next, we need one more alliance, so we get an association with sub-alliances.

This works with the data provided. I checked a lot of inputs and got the correct previous / next results. When using, make sure that you have received ALL 146 instances for replacement.

 SELECT * FROM ( SELECT t1.* FROM t t1, ( SELECT * FROM t WHERE id = 146 ) t2 WHERE t1.statusid = t2.statusid AND t1.dateCreated >= t2.dateCreated AND t1.id <> 146 UNION SELECT t1.* FROM t t1, ( SELECT * FROM t WHERE id = 146 ) t2 WHERE t1.statusid < t2.statusid ORDER BY t1.statusid DESC, t1.dateCreated LIMIT 1 ) UNION SELECT * FROM ( SELECT t1.* FROM t t1, ( SELECT * FROM t WHERE id = 146 ) t2 WHERE t1.statusid = t2.statusid AND t1.dateCreated <= t2.dateCreated AND t1.id <> 146 UNION SELECT t1.* FROM t t1, ( SELECT * FROM t WHERE id = 146 ) t2 WHERE t1.statusid > t2.statusid ORDER BY t1.statusid, t1.dateCreated DESC LIMIT 1 ) ORDER BY statusid, dateCreated DESC ; 

Answer 2

 select id from theTable where id>@id order by id desc limit 1 union select id from theTable where id<@id order by id desc limit 1