SQLite select next and previous row based on a where clause

sql, sqlite

Solution

This problem is a lot more complicated than it first appears. The two order by fields have to be handled separately and then combined with a union and grab the appropriate result. To get both previous and next, we need another union, so we end up with a union with sub-unions.

This works with the supplied data. I tested many inputs and got the right previous/next outputs. When using, make sure you get ALL instances of `146` to replace.

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
;

Problem

I want to be able to `get` the next and previous row using 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 example: Given id 46 I would like to return ids 160 (the previous one) and 145 (the next one) Given id 160 I would like to return ids 161 (the previous one) and 46 (the next one) etc... Be aware that the 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 in 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+ this would be fairly trivial! EDIT: Here's the same test data script but for SQLite which is 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 Please note that the data is not a good example so I have change the `id 146` to `46`

Original source